oracle 查询重复记录语句

最新推荐文章于 2024-07-11 16:22:36 发布

原创最新推荐文章于 2024-07-11 16:22:36 发布 · 1.3k 阅读

2 ·

CC 4.0 BY-SA版权

收集整理专栏收录该内容

9 篇文章

订阅专栏

本文详细介绍了如何使用SQL语句来查找并删除数据库表中的重复记录，包括基于单个字段和多个字段的重复记录处理方法。通过具体示例，展示了如何仅保留rowid最小的记录，确保数据的唯一性和准确性。

1、查找表中多余的重复记录，根据单个字段
select * from tskugtplu
where plucode in (select plucode from plucode group by plucode having count(peopleId) > 1);

delete from tskugtplu
where plucodein (select plucode from tskugtplugroup by plucode having count(plucode) > 1)and rowid not in (select min(rowid) from tskugtplu group by plucode having count(plucode)>1);

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

select * from tskugtplu a
where (a.plucode,a.depcode) in (select plucode,depcode from tskugtplu group by plucode,depcode having count(*) > 1);

-------只留rowid最小的记录

delete from tskugtplu a
where (a.plucode,a.depcode) in (select a.plucode,a.depcode from tskugtplu group by a.plucode,a.depcode having count(*) > 1) and rowid not in (select min(rowid) from tskugtplu group by a.plucode,a.depcodehaving count(*)>1);

select * from tskugtplua
where (a.plucode,a.depcode) in (select a.plucode,a.depcode from tskugtplugroup by a.plucode,a.depcode having count(*) > 1) and rowid not in (select min(rowid) from tskugtplu group by a.plucode,a.depcode having count(*)>1)---不包含rowid最小的记录