正在加载中...
sql server怎么删除某一个或多个字段重复的记录?
有时sql server数据库表的记录中,存在一些重复的值,而这些记录却不一定每一个字段都重复,我们根据实际情况决定排重的条件,可以是一个字段,也可以是多个字段,然后将筛选出来的结果进行计数和删除。万维景盛提供阿里云服务器代购服务,如果您通过我司,可以享受折上折优惠,同时免费享受全能运行环境和豪华虚拟主机控制面板。
思路原理:
逻辑很简单,第一步就是把重复条数大于1的全部都查出来。
SELECT * FROM TableName WHERE RepeatFiled IN ( SELECT RepeatFiled FROM TableName GROUP BY RepeatFiled HAVING COUNT(RepeatFiled) > 1 AND ID NOT IN ( SELECT MIN(ID) FROM TableName GROUP RepeatFiled HAVING COUNT(RepeatFiled) > 1 ) )
第二步:删除重复记录,只保留一条
delete FROM TableName WHERE RepeatFiled IN ( SELECT RepeatFiled FROM TableName GROUP BY RepeatFiled HAVING COUNT(RepeatFiled) > 1 AND ID NOT IN ( SELECT MIN(ID) FROM TableName GROUP RepeatFiled HAVING COUNT(RepeatFiled) > 1 ) )
实战分析:
1、查找表中多余的重复记录,重复记录是根据单个字段(bid)来判断
select * from [business] where bid in (select bid from [business] group by bid having count(bid) > 1)
2、删除表中多余的重复记录,重复记录是根据单个字段(bid)来判断,只留有rowid最小的记录
delete from [business] where bid in (select bid from [business] group by bid having count(bid) > 1) and rowid not in (select min(rowid) from [business] group by bid having count(bid )>1)
3、查找表中多余的重复记录(多个字段)
select * from [business] a where (a.bid,a.seq) in (select bid,seq from [business] group by bid,seq having count(*) > 1)
4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from [business] a where (a.bid,a.seq) in (select bid,seq from [business] group by bid,seq having count(*) > 1) and rowid not in (select min(rowid) from [business] group by bid,seq having count(*)>1)
另一种简单方法应用举例:
select * from Sys_RlReport WHERE Id NOT IN (SELECT MIN(Id) FROM Sys_RlReport GROUP BY employeename,bus) order BY employeename,bus
DELETE Sys_RlReport WHERE Id NOT IN (SELECT MIN(Id) FROM Sys_RlReport GROUP BY employeename,bus)
select * from [dbo].[Cards] WHERE Id NOT IN (SELECT MIN(Id) FROM [dbo].[Cards] GROUP BY cardno) order by id
delete from [dbo].[Cards] WHERE sysId NOT IN (SELECT MIN(sysId) FROM [dbo].[Cards] GROUP BY cardno)