此文章主要向大家介绍的是SQL Server查找与删除重复记录的实际操作方法，你是否对如何正确查出字段dd中有重复的记录的实际操作有不解之处？即要知道哪些记录是重复的,怎么弄?
执行：
- select dd,count(*) from table group by dd having count(*)>1
如何用sql 查找两个字段重复的记录，并列出重复记录
表名为CJB, 列出其中XH和KCMC字段都重复的记录
执行：
- select * from CJB a join (
- select XH,KCMC from CJB group by XH,KCMC
- having count(*)>1) b on a.XH=b.XH and a.KCMC=b.KCMC order by
- a.KCMC ,a.XH
实例：
- select productID,searchkay from shortkay group by searchkay,productID having count(*) > 1 order by searchkay
等同于下面这句：
- select a.ID,a.productID,a.searchkay from shortkay a join (select productID,searchkay from shortkay group by productID,
- searchkay having count(*)>1) b on a.productID=b.productID and a.searchkay=b.searchkay order by a.searchkay, a.productID
1、SQL Server查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
- select * from people
- where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
- delete from people
- where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
- and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1
)
3、查找表中多余的重复记录（多个字段）
- select * from vitae a
- where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
- delete from vitae a
- where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
- and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
5、SQL Server查找表中多余的重复记录（多个字段），不包含rowid最小的记录
- select * from vitae a
- where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
- and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
以上的相关内容就是对SQL Server查找和删除重复记录的方法的介绍，望你能有所收获。
【编辑推荐】
