当前位置:  数据库>mssql

删除数据库中重复记录方法介绍及代码参考

 
分享到:
    发布时间:2013-7-3  


    本文导语: 重复数据,通常有两种:一是完全重复的记录,也就是所有字段的值都一样;二是部分字段值重复的记录 一. 删除完全重复的记录完全重复的数据,通常是由于没有设置主键/唯一键约束导致的。测试数据:代码如下: if OBJECT_ID(...

重复数据,通常有两种:一是完全重复的记录,也就是所有字段的值都一样;二是部分字段值重复的记录

一. 删除完全重复的记录

完全重复的数据,通常是由于没有设置主键/唯一约束导致的。
测试数据:

代码如下:

if OBJECT_ID('duplicate_all') is not null
drop 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 all
select 1,100,'aaa' union all
select 1,100,'aaa' union all
select 1,100,'aaa' union all
select 1,100,'aaa' union all
select 2,200,'bbb' union all
select 3,300,'ccc' union all
select 4,400,'ddd' union all
select 5,500,'eee'
GO


(1) 借助临时表

利用DISTINCT得到单条记录,删除源数据,然后导回不重复记录。
如果表不大的话,可以把所有记录导出一次,然后truncate表后再导回,这样可以避免delete日志操作。

代码如下:
if OBJECT_ID('tempdb..#tmp') is not null
drop 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 null
drop 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 all
select 2,100,'aaa' union all
select 3,100,'aaa' union all
select 4,100,'aaa' union all
select 5,500,'eee'
GO

(1) 唯一索引

唯一索引有个忽略重复建的选项,在创建主键约束/唯一键约束时都可以使用这个索引选项。

代码如下:
if OBJECT_ID('tmp') is not null
drop 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_col

SQL删除重复数据只保留一条
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=-1
where peopleId in (select peopleId from vitae group by peopleId

  • 本站(WWW.169IT.COM)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
    本站(WWW.169IT.COM)站内文章除注明原创外,均为转载,整理或搜集自网络.欢迎任何形式的转载,转载请注明出处.
    转载请注明:文章转载自:[169IT-IT技术资讯]
    本文标题:删除数据库中重复记录方法介绍及代码参考
相关文章推荐:
  • C++ stl队列Queue用法介绍:删除,插入等操作代码举例
  • Jquery删除元素的代码举例
  • c++ STL关联式容器Map成员函数介绍及查找(find()),插入(insert()),删除(erase())等操作代码举例
  • php 删除记录同时删除图片文件的代码
  • c# 删除空文件夹的代码
  • ThinkPHP实现批量删除数据的代码实例
  • beyond_xiruo(希偌):请问我要删除某个DIV,那在<%.....%>之间的代码该怎么写???多谢!
  • C#下实现创建和删除目录的实例代码
  • jquery代码-如何显示或删除input域中的默认值
  • php删除数组元素示例代码
  • SQL 级联删除 级联更新的代码
  • sed删除文件中的一行内容的脚本代码
  • java 删除数组元素与删除重复数组元素的代码
  • 使用SQL Server判断文件是否存在并删除的实例代码
  • PHP删除HTMl标签的代码
  • PHP多重判断删除文件函数代码
  • c# 文件夹操作(创建或删除)的实现代码
  • 删除JAVA集合中元素的实现代码
  • python2.7删除文件夹和删除文件代码实例
  • oracle命令行删除与创建用户的代码
  • PHP实现删除非站内外部链接实例代码


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2017,169IT.COM,E-mail:www_169it_com#163.com(请将#改为@)

    浙ICP备11055608号