plsql排重复行总结一（Row PLSQL repeat line 1）.docVIP

plsql排重复行总结一（Row PLSQL repeat line 1） Summarizes some ways to delete duplicate records. In Oracle, you can delete duplicate records by means of a unique ROWID. You can also create temporary tables to implement. This refers only to several simple and practical methods, I hope to share with you (in table employee). SQL desc employee Name Null? Type? ------------------------------------------------------------------- Emp_id NUMBER (10) Emp_name VARCHAR2 (20) Salary NUMBER (10,2) You can query duplicate records through the following statement: SQL select * from employee; EMP_ID EMP_NAME SALARY ------------------------------------------------------------ 1 sunshine 10000 1 sunshine 10000 2 Semon 20000 2 Semon 20000 3 XYZ 30000 2 Semon 20000 SQL select distinct * from employee; EMP_ID EMP_NAME SALARY ------------------------------------------------------------ 1 sunshine 10000 2 Semon 20000 3 XYZ 30000 SQL, select *, from, employee, group, by, emp_id, emp_name, salary, having, count (*) 1 EMP_ID EMP_NAME SALARY ------------------------------------------------------------ 1 sunshine 10000 2 Semon 20000 SQL select * from employee E1 Where, ROWID, in (select, max (ROWID), from, employe, E2 Where e1.emp_id=e2.emp_id and E1.emp_name=e2.emp_name, and, e1.salary=e2.salary); EMP_ID EMP_NAME SALARY ------------------------------------------------------------ 1 sunshine 10000 3 - 30 2 semon. 2. 删除的几种方法. (1) 通过建立临时表来实现 sql create table temp _ select emp have separate from employee) ( truncate table employee; (清空employee表的数据) sql insert into employee select * from time _ emp (再将临时表里的内容插回来) (2) 通过唯一rowid实现删除重复记录.在oracle中, 每一条记录都有一个rowid, rowid在整个数据库中是唯一的, rowid确定了每条记录是在oracle中的哪一个数据文件、块、行上.在重复的记录中, 可能所有列的内容都相同, 但rowid不会相同, 所以只要确定出重复记录中那些具有最大或最小rowid的就可以了, 其余全部删除. sql delete from employee where rowid e2 not in select max (e1.rowid) from employee e1 where e1.emp _ id = e2.emp _ id and e1.emp _ name = e2.emp _ name and e1.salary = e2.salary); - 这里用min (rowid) 也可以. sql delete from emplo