Oracle查找、删除表的重复记录，快速获取去重之后的数据

1、查找表中多余的重复记录，重复记录是根据单个字段（ID）来判断

SELECT * FROM 表 WHERE ID IN (SELECT ID FROM 表 GROUP BY ID HAVING COUNT(1) > 1);

2、删除表中多余的重复记录，重复记录是根据单个字段（ID）来判断，只留有ROWID最小的记录

DELETE FROM 表 WHERE (ID) IN ( SELECT ID FROM 表 GROUP BY ID HAVING COUNT(1) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM 表 GROUP BY ID HAVING COUNT(1) > 1);

3、查找表中多余的重复记录（多个字段）

SELECT * FROM 表 A WHERE (A.ID,A.SEQ) IN (SELECT ID,SEQ FROM 表 GROUP BY ID,SEQ HAVING COUNT(1) > 1);

4、删除表中多余的重复记录（多个字段），只留有ROWID最小的记录

DELETE FROM 表 A WHERE (A.ID,A.SEQ) IN (SELECT ID,SEQ FROM 表 GROUP BY ID,SEQ HAVING COUNT(1) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM 表 GROUP BY ID,SEQ HAVING COUNT(1)>1);

5、快速获取去重之后的数据，重复记录是根据单个字段（ID）来判断，只留有ROWID最小的记录

select * from 表 where ROWID IN (SELECT MIN(ROWID) FROM 表 GROUP BY ID HAVING COUNT(1) > 1);