SQL：删除重复数据，只保留一条

骑猪看日落 2021-11-22 15:38 313阅读 0赞

### SQL：删除重复数据，只保留一条 ###

[sql][] 2010-09-10 16:03:33 阅读82 评论0  字号：大**中**小 订阅

在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢  
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断  
select \* from people  
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)  
  
2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录  
delete from people  
where   peopleName in (select peopleName    from people group by peopleName      having count(peopleName) > 1)  
and   peopleId not in (select min(peopleId) from people group by peopleName     having count(peopleName)>1)

2.1 删除多列条件的

delete   from  db\_CR\_MainInfo  
where CR\_ID not in  
(  
select Min(CR\_ID) from db\_CR\_MainInfo where addtime >'2011-10-08'  
group by CommunityName,address,ActivitiesStartTime,ActivitiesEndTime  
)  
and addtime >'2011-10-08'

3、查找表中多余的重复记录（多个字段）  
select \* from vitae a  
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(\*) > 1)  
  
4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录  
delete from vitae a  
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(\*) > 1)  
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(\*)>1)  
  
5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录  
select \* from vitae a  
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(\*) > 1)  
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(\*)>1)    
  
6.消除一个字段的左边的第一位：  
  
update tableName set \[Title\]=Right(\[Title\],(len(\[Title\])-1)) where Title like '村%'  
  
7.消除一个字段的右边的第一位：  
  
update tableName set \[Title\]=left(\[Title\],(len(\[Title\])-1)) where Title like '%村'  
  
8.假删除表中多余的重复记录（多个字段），不包含rowid最小的记录  
update vitae set ispass=-1  
where peopleId in (select peopleId from vitae group by peopleId,seq having count(\*) > 1) and seq in （select seq from vitae group by peopleId,seq having count(\*) > 1）  
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(\*)>1)

转载于:https://www.cnblogs.com/yongheng178/archive/2010/12/18/1910109.html

[sql]: http://ifhaveif.blog.163.com/blog/#m=0&t=1&c=fks_087074093086086066082080084095083080084068085085084068