表中多余的重复记录的查找及删除
表中多余的重复记录的查找及删除
1、查找表中多余的重复记录,重复记录是根据单个字段(id)来判断
[sql]
select * from Table_2 where id in(select id from Table_2 group by id having count(id)>1)
--通过子查询判断重复的记录
--select id from Table_2 group by id having count(id)>1 取出有重复的ID
--select id from Table_2 group by id having count(id)<=1 取出没有有重复的ID
2、删除表中多余的重复记录,重复记录是根据单个字段(Id)来判断,只留有id最小的记录
其实这个问题和第一个问题是相关的,只要第一个问题解决了这个问题也就解决了。
[sql]
delete from Table_2
where Id in
(select Id from Table_2 group by Id having count(Id) > 1)
--取出重复的记录
and
id not in (select min(id) from Table_2 group by Id having count(Id )>1)
--排除id最小的记录
3、查找表中多余的重复记录(多个字段,type,num),需要借助于临时表
保留重复记录中的第一条记录
[sql]
select type,num from Table_2 group by type,num having count(*)>1
--重复的记录
select identity(int,1,1) as autoID, * into #Tmp from Table_2 --建立一个临时表,新加一个字段autoID
select min(autoID) as autoID into #Tmp2 from #Tmp group by type,num having count(*)>1
--在临时表中取出最小的autoID
select * from #Tmp where autoID in(select autoID from #tmp2)--
--最后一个select即得到了type,num重复的结果集(但多了一个autoID字段,实际写时可以写在select子句中省去此列)
4、删除表中多余的重复记录(多个字段),只留有id最小的记录
这个问题其实是第三个问题相关的,也需要借助于临时表。
5对于完全重复的记录,也即所有字段均重复的记录
查询方式
[sql]
select distinct * from Table_2
如果该表需要删除重复的记录(重复记录保留1条),可以借助临时表,可以按以下方法删除
[sql]
select distinct * into #Tmp from Table_2
drop table Table_2
select * into Table_2 from #Tmp
drop table #Tmp
摘自 xuexiaodong2009的专栏