Oracle里删除重复记录，保留一项

我们在使用数据库的时候，有时数据会有所重复，当我们只需要一项数据时，不需要显示重复的记录时

如下就有SQL代码：

 1 --查找表中多余的重复记录，重复记录是根据单个字段来判断
 2 select * from people where peopleId in(select peopleId from people group by peopleId
 3 having count(peopleId)>1);
 4 
 5 --删除表中多余的重复记录，重复记录是根据单个字段来判断，只保留rowid最小的记录
 6 delete from people where peoplename in(select peoplename from people group by peoplename  having count(peoplename)>1)
 7 and peopleId not in (select min(peopleId) from people group by peoplename  having count(peoplename)>1);
 8 
 9 --查找表中多余的重复记录（多个字段）
10 select * from vitae a 
11 where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*)>1);
12 
13 --删除表中多余的重复记录（多个字段），只保留rowid最小的记录
14 delete from vitae a 
15 where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*)>1)
16 and rowid not in(select min(rowid) from vitae group by peopleId,seq having count(*)>1);

View Code

 

转载于:https://www.cnblogs.com/suyun0702/p/8204924.html