sql删除重复数据的详细方法

2023年 4月 18日 29.8k 0

一. 删除完全重复的记录 完全重复的数据,通常是由于没有设置主键/唯一键约束导致的。 测试数据: 复制代码 代码如下: 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

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论