Oracle 数据去重

在Oracle数据库中删除重复数据

一，查询及删除重复记录的SQL语句

Person01表：

1. 查询表中多余的重复数据，根据ID字段来判断是否重复

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

2. 根据ID字段来判断是否重复删除表中多余的重复记录、重复记录根据ID字段来判断，多条重复记录只保留 ROWID 最小的那条

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

3. 查询表中多余的重复数据，根据多个字段来判断是否重复

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

4. 删除表中多余的重复记录，根据多个字段来判断是否重复