SQL查询重复数据

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)