1.所有字段均重复的记录（重复记录保留一条）

Select distinct * into #Tmp from tblName

Drop table tblName

Select * into tblName from #Tmp

Drop table #Tmp

设计不周产生的，增加唯一索引可解决

2.所有字段均重复的记录（重复记录保留一条）

Select distinct * into #Tmp from tblName

Drop table tblName

Select * into tblName from #Tmp

Drop table #Tmp

设计不周产生的，增加唯一索引可解决

3.保留ID最小的记录,删除其它行

     Delete from tblName where ID not in (select min(ID) from tblName group Name)

     Delete from tblName t inner join (select min(ID) id,Name from tblName group by Name) b on t.name=b.name and t.id<>b.id

     Delete from tblName where exists (select * from tblName where name=t.name and id<t.id)

4.只保留ID最大的记录

1.   Delete from tblName where ID not in (select max(ID) from tblName group by Name having count(*)>1)
2.   Delete from tblName t inner join (select Name,max(ID) id from tblName group by name) b on t.name=b.name and t.id<>b.id
3.   Delete from tblName t where exists (select * from tblName where name=t.name and id>t.id)

删除重复记录的SQL语句,布布扣,bubuko.com

删除重复记录的SQL语句