Sql删除表中多余的重复记录

删除表中多余的重复记录,重复记录是根据单个字段(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) 

如果 peopleId 是uniqueidentifier类型 则将

select min(peopleId) from people group by peopleName     having count(peopleName)>1

改为

select min(convert(varchar(36),peopleId)) from people group by peopleName     having count(peopleName)>1

posted @ 2017-10-31 12:07  码农丁  阅读(298)  评论(0编辑  收藏  举报