Oracle開發(fā)技術(shù)：Oracle刪除重復(fù)記錄的幾種方式 Oracle刪除重復(fù)記錄的幾種方式 　　[html] 　　Oracle刪除重復(fù)記錄的幾種方式 　　如果把一個(gè)文件多次導(dǎo)入數(shù)據(jù)庫，可能會(huì)引入重復(fù)記錄，那么有哪些方法可以刪除重復(fù)記錄呢? 　　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) 最常用的方法，但是數(shù)據(jù)量大的話執(zhí)行會(huì)很長時(shí)間 　　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: 對(duì)于大表這是最有效的方法 　　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 對(duì)資源使用比較合理，特別對(duì)于大表。但是如果需要回滾則會(huì)產(chǎn)生大量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.