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

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