Oracle重复数据相关

前端之家收集整理的这篇文章主要介绍了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,id2 having count(*)>1);

猜你在找的Oracle相关文章