Oracle删除重复记录的几种方式
原文链接:http://www.javaarch.net/jiagoushi/706.htm
Oracle删除重复记录的几种方式 如果把一个文件多次导入数据库,可能会引入重复记录,那么有哪些方法可以删除重复记录呢? REATE TABLE tbl_test( SER_NO NUMBER, FST_NM VARCHAR2(30), DEPTID NUMBER, CMNT VARCHAR2(30)); INSERT INTO tbl_test VALUES(1, 'aaaaa', 2004, 'xxx'); INSERT INTO tbl_test VALUES(2, 'bbbbb', 2005, 'yyy'); INSERT INTO tbl_test VALUES(1, 'aaaaa', 2004, 'xxx'); INSERT INTO tbl_test VALUES(1, 'aaaaa', 2004, 'xxx'); INSERT INTO tbl_test VALUES(3, 'ccccc', 2005, 'zzz'); INSERT INTO tbl_test VALUES(2, 'bbbbb', 2005, 'yyy'); 1.Using MIN(rowid) 最常用的方法,但是数据量大的话执行会很长时间 DELETE FROM tbl_test WHERE ROWID NOT IN (SELECT MIN (ROWID) FROM tbl_test GROUP BY ser_no, fst_nm, deptid, cmnt); 2.Using MIN(rowid) & Join 跟第一条差不多 DELETE FROM tbl_test t WHERE t.ROWID NOT IN (SELECT MIN (b.ROWID) FROM tbl_test b WHERE b.ser_no = t.ser_no AND b.fst_nm = t.fst_nm AND b.deptid = t.deptid AND b.cmnt = t.cmnt); 3.Using Subquery DELETE FROM tbl_test WHERE ser_no IN (SELECT ser_no FROM tbl_test GROUP BY ser_no, fst_nm, deptid, cmnt HAVING COUNT (*) > 1) AND fst_nm IN (SELECT fst_nm FROM tbl_test GROUP BY ser_no, fst_nm, deptid, cmnt HAVING COUNT (*) > 1) AND deptid IN (SELECT deptid FROM tbl_test GROUP BY ser_no, fst_nm, deptid, cmnt HAVING COUNT (*) > 1) AND cmnt IN (SELECT cmnt FROM tbl_test GROUP BY ser_no, fst_nm, deptid, cmnt HAVING COUNT (*) > 1) AND ROWID NOT IN (SELECT MIN (ROWID) FROM tbl_test GROUP BY ser_no, fst_nm, deptid, cmnt HAVING COUNT (*) > 1) 4. Using Nested Subqueries DELETE FROM tbl_test a WHERE (a.ser_no, a.fst_nm, a.deptid, a.cmnt) IN (SELECT b.ser_no, b.fst_nm, b.deptid, b.cmnt FROM tbl_test b WHERE a.ser_no = b.ser_no AND a.fst_nm = b.fst_nm AND a.deptid = b.deptid AND a.cmnt = b.cmnt AND a.ROWID > b.ROWID); 5. Using Analytic Fucntions: 对于大表这是最有效的方法 DELETE FROM tbl_test WHERE ROWID IN (SELECT rid FROM (SELECT ROWID rid, ROW_NUMBER () OVER (PARTITION BY ser_no, fst_nm, deptid, cmnt ORDER BY ROWID) rn FROM tbl_test)WHERE rn <> 1); 6. CREATE-DROP-RENAME 对资源使用比较合理,特别对于大表。但是如果需要回滚则会产生大量undo日志信息。 CREATE TABLE tbl_test1 NOLOGGING AS SELECT tbl_test .* FROM tbl_test tbl_test WHERE ROWID IN (SELECT rid FROM (SELECT ROWID rid, ROW_NUMBER() OVER (PARTITION BY ser_no, fst_nm, deptid, cmnt ORDER BY ROWID) rn FROM tbl_test) WHERE rn=1); DROP TABLE tbl_test; --drop the original table with lots of duplicate RENAME tbl_test1 TO tbl_test; -- your original table without duplicates.