一. 删除完全重复的记录 完全重复的数据，通常是由于没有设置主键/唯一键约束导致的。 测试数据： 复制代码 代码如下: if OBJECT_ID('duplicate_all') is not null drop table duplicate_all GO create t
一. 删除完全重复的记录
完全重复的数据，通常是由于没有设置主键/唯一键约束导致的。测试数据：复制代码 代码如下:if OBJECT_ID('duplicate_all') is not nulldrop table duplicate_all GO create table duplicate_all ( c1 int, c2 int, c3 varchar(100) ) GO insert into duplicate_all select 1,100,'aaa' union allselect 1,100,'aaa' union allselect 1,100,'aaa' union allselect 1,100,'aaa' union allselect 1,100,'aaa' union allselect 2,200,'bbb' union allselect 3,300,'ccc' union allselect 4,400,'ddd' union allselect 5,500,'eee'GO
(1) 借助临时表
利用DISTINCT得到单条记录，删除源数据，然后导回不重复记录。如果表不大的话，可以把所有记录导出一次，然后truncate表后再导回，这样可以避免delete的日志操作。复制代码 代码如下:if OBJECT_ID('tempdb..#tmp') is not nulldrop table #tmp GO select distinct * into #tmp from duplicate_all where c1 = 1 GO delete duplicate_all where c1 = 1 GO insert into duplicate_all select * from #tmp(2) 使用ROW_NUMBER复制代码 代码如下:with tmp as( select *,ROW_NUMBER() OVER(PARTITION BY c1,c2,c3 ORDER BY(getdate())) as num from duplicate_all where c1 = 1 ) delete tmp where num > 1
如果多个表有完全重复的行，可以考虑通过UNION将多个表联合，插到一个新的同结构的表，SQL Server会帮助去掉表和表之间的重复行。
二. 删除部分重复的记录
部分列重复的数据，通常表上是有主键的，可能是程序逻辑造成了多行数据列值的重复。测试数据：复制代码 代码如下:if OBJECT_ID('duplicate_col') is not nulldrop table duplicate_col GO create table duplicate_col ( c1 int primary key, c2 int, c3 varchar(100) ) GO insert into duplicate_col select 1,100,'aaa' union allselect 2,100,'aaa' union allselect 3,100,'aaa' union allselect 4,100,'aaa' union allselect 5,500,'eee'GO
(1) 唯一索引
唯一索引有个忽略重复建的选项，在创建主键约束/唯一键约束时都可以使用这个索引选项。复制代码 代码如下:if OBJECT_ID('tmp') is not nulldrop table tmp GO create table tmp ( c1 int, c2 int, c3 varchar(100), constraint UQ_01 unique(c2,c3) with(IGNORE_DUP_KEY = ON) ) GO insert into tmp select * from duplicate_col select * from tmp(2) 借助主键/唯一键来删除通常会选择主键/唯一键的最大/最小值保留，其他行删除。以下只保留重复记录中c1最小的行。复制代码 代码如下:delete from duplicate_col where exists(select 1 from duplicate_col b where duplicate_col.c1 > b.c1 and (duplicate_col.c2 = b.c2 and duplicate_col.c3 = b.c3)) --或者 复制代码 代码如下:delete from duplicate_col where c1 not in (select min(c1) from duplicate_col group by c2,c3)如果要保留重复记录中的第N行，可以参考05.取分组中的某几行。(3) ROW_NUMBER和删除完全重复记录的写法基本一样。复制代码 代码如下:with tmp as( select *,ROW_NUMBER() OVER(PARTITION BY c2,c3 ORDER BY(getdate())) as num from duplicate_col ) delete tmp where num > 1 select * from duplicate_colSQL删除重复数据只保留一条 （下面的代码，很多网友反馈错误，大家多测试）用SQL语句,删除掉重复项只保留一条在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢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 peopleName in (select peopleName from people group by peopleName having count(peopleName) > 1) and peopleId not in (select min(peopleId) from people group by peopleName having count(peopleName)>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、查找表中多余的重复记录（多个字段），不包含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) 6.消除一个字段的左边的第一位：update tableName set [Title]=Right([Title],(len([Title])-1)) where Title like '村%'7.消除一个字段的右边的第一位：update tableName set [Title]=left([Title],(len([Title])-1)) where Title like '%村'8.假删除表中多余的重复记录（多个字段），不包含rowid最小的记录 update vitae set ispass=-1where peopleId in (select peopleId from vitae group by peopleId
