SQL查询重复数据

1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

select * from 表名称 where peopleId in (select 字段名 from 表名称 group by 字段名 having count(字段名) > 1)

2、查找表中多余的重复记录（多个字段）

select * from 表名称 a where (a.字段名,a.字段名) in (select 字段名,字段名 from 表名称 group by 字段名,字段名 having count(*) > 1)

假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集

select identity(int,1,1) as autoID, * into #Tmp from tableName

select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID

select * from #Tmp where autoID in(select autoID from #tmp2)

最后一个select即得到了Name，Address不重复的结果集（但多了一个autoID字段，实际写时可以写在select子句中省去此列）

 

3查询重复

select * from tablename where id in (select id from tablename group by id having count(id) > 1)

 

————————————————
版权声明：本文为CSDN博主「有颗程序员的心」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_42482606/article/details/109644019