找出表中的重复记录和重复记录数

来源：互联网发布：网页域名注册编辑：程序博客网时间：2024/05/17 23:00

如果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 TABLE1drop table TABLE1

查找重复记录方法

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