Oracle重复数据相关

Oracle中对重复数据的查询、删除

1、查找重复记录，根据id判断

select * from table where id in (select id from table group by id having count(id) > 1);

2、删除多余重复记录，保留rowid最小记录，根据id判断

DELETE from table WHERE (id) IN ( SELECT id FROM table GROUP BY id HAVING COUNT(id) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM table GROUP BY id HAVING COUNT(*) > 1);

3、查找重复记录，根据多个字段判断

select * from table a where (a.id,a.id2) in(select Id,seq from table group by id,id2 having count(*) > 1);

4、删除重复记录，保留rowid最小记录，根据多个字段判断

delete from table a where (a.id,a.id2) in (select id,id2 from table group by id,id2 having count(*) > 1) and rowid not in (select min(rowid) from table group by id,id2 having count(*)>1);

5、查找重复记录，不包含rowid最小记录，根据多个字段判断

select * from table a where (a.id,a.id2) in (select Id,seq from table group by id,id2 having count(*) > 1) and rowid not in (select min(rowid) from table group by id,id2 having count(*)>1);