当前位置: 数据库>sqlserver
sql server 查询重复记录的多种方法
来源: 互联网 发布时间:2014-08-29
本文导语: 1、查找表中多余的重复记录,根据单个字段(peopleId)来判断 select * from people where peopleId in (select peopleId from people group by peopleId having count (peopleId) > 1) 2、删除表中多余的重复记录,根据单个字段(peopleId)来判断,只留有rowid...
1、查找表中多余的重复记录,根据单个字段(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) --by www.
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、查找表中多余的重复记录(多个字段),不包含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 重复记录的文章,大家还可以参考:
sql server删除表中重复数据行的方法
sql server 重复记录的取最新一笔的实现方法
sql server删除重复记录且只余一条的例子
SQL Server删除重复数据的几种方法
去除重复数据的SQL语句
如何在SQL Server2008中删除重复记录