oracle 查找重复语句,oracle 查询重复记录语句

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

liaxi z

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

阅读量1.5k

点赞数

文章标签： oracle 查找重复语句

本文介绍了如何使用SQL查询在tskugtplu表中查找并删除多余的重复记录，针对单个字段和多个字段的情况提供了两种策略。通过子查询找出符合条件的plucode或(plucode, depcode)组合，并利用MIN(rowid)保留唯一行，确保数据一致性。

摘要由CSDN通过智能技术生成

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最小的记录