sql 重复记录和重复记录数

如果TABLE1有两个column adress和pepole,
那么下面的SQL可以找出TABLE1里的重复记录和重复记录数

 

create table TABLE1(adress nvarchar(10),pepole nvarchar(10))
insert TABLE1 select '宁波',   '张三(NB)'
union all select '宁波',   '李四(NB)'
union all select '宁波',   '王五(NB)'
union all select '杭州',   '张三(HZ)'
union all select '杭州',   '李四(HZ)'
union all select '杭州',   '王五(HZ)'
union all select '杭州',   '王五2(HZ)'
union all select '杭州',   '王五3(HZ)'
union all select '温州',   '张三(WZ)'
union all select '温州',   '李四(WZ)'
select * from TABLE1
drop table TABLE1

----只有一列有重复的
SELECT COUNT(*) AS RowNumber,adress
FROM TABLE1 T1
WHERE
 (SELECT COUNT(*)
 FROM TABLE1 T2
 WHERE T1.adress = T2.adress )>1
GROUP BY adress

---两列有重复的
SELECT COUNT(*) AS RowNumber,adress,pepole
FROM TABLE1 T1
WHERE
 (SELECT COUNT(*)
 FROM TABLE1 T2
 WHERE T1.adress = T2.adress AND T1.pepole = T2.pepole)>1
GROUP BY adress,pepole