重复记录-题外

  • 重复记录-查找
  • 重复记录-删除
  • 重复记录-避免
  • 重复记录-题外

一. 删除完全重复的记录

  • 重复记录-查找
  • 重复记录-删除
  • 重复记录-避免
  • 重复记录-题外

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

要最简单最高效地发现重复记录,每一条记录应该有自身的标识,我们通常用主键作为标识,比如我们常说的“记录ID”。

步骤二、对目的表需要保持唯一的一个或多个字段建立索引或联合索引,确保索引不重复,且选中“忽略重复键”。

如果多个表有完全重复的行,可以考虑通过UNION将多个表联合,插到一个新的同结构的表,SQL
Server会帮助去掉表和表之间的重复行。

建立一个表,并插入几条记录,如下:

步骤三、将源表内容复制到目的表,可利用导入导出工具,也可利用 insert
into select from。

二. 删除部分重复的记录

if exists(select name from sysobjects where name=’foo’ and xtype=’U’)
    drop table [foo]
go

步骤四、清空源表,可用 delete 或 truncate
table,再将目的表内容复制到源表,可用导入导出工具,也可利用 insert
into select from。

部分列重复的数据,通常表上是有主键的,可能是程序逻辑造成了多行数据列值的重复。测试数据:复制代码 代码如下: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

create table [foo]
(
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [sName] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [sClass] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL
) on [PRIMARY]
go

分析:

(1) 唯一索引

insert into foo(sName, sClass) values(‘张三’, ‘1’)
insert into foo(sName, sClass) values(‘李四’, ‘1’)
insert into foo(sName, sClass) values(‘李四’, ‘2’)
insert into foo(sName, sClass) values(‘王五’, ‘2’)
insert into foo(sName, sClass) values(‘王五’, ‘2’)
insert into foo(sName, sClass) values(‘王五’, ‘2’)

一、多次写入表,在记录大的情况下,我还没有测试过是否有较高的效率。

唯一索引有个忽略重复建的选项,在创建主键约束/唯一键约束时都可以使用这个索引选项。复制代码 代码如下: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、查找表中多余的重复记录,重复记录是根据单个字段来判断
select * from people where peopleId in (select peopleId from people
group by peopleId having count(peopleId) 1)
2、删除表中多余的重复记录,重复记录是根据单个字段来判断,只留有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

我们定义:sName 与 sClass
相同即为重复记录,可以看出,第四、五、六条记录重复。

二、哪种情况下算作记录重复,是我们自定义的,如果我们的条件是很多个字段联合起来不能重复,那么索引势必很大,是否具有高效也值得考虑。

那么如何找出这些重复记录呢?

三、可能会丢失标识(常说的
ID),记录的复制,可能会对标识重新排序,大多数情况下,我们不希望标识有所改动。要做到这点,简单的方法是利用导入导出工具,并选中“启用标识插入”。

select L.* from foo L inner join foo R on L.id<>R.id and
L.sName=R.sName and L.sClass=R.sClass

 

将表 foo 看作两个表:L 和 R,使用内联接选取重复记录,L.id<>R.id
表示不是同一条记录,L.sName=R.sName and L.sClass=R.sClass
表示定义为重复的条件。

  • 重复记录-查找
  • 重复记录-删除
  • 重复记录-避免
  • 重复记录-题外

列表结果我们可能会发现有些记录显示了多次,不便于观察,我们可以过滤。


select distinct * from (
select L.* from foo L inner join foo R on L.id<>R.id and
L.sName=R.sName and L.sClass=R.sClass) a

将结果看成一个表 a,再用 distinct 过滤。

  • 重复记录-查找
  • 重复记录-删除
  • 重复记录-避免
  • 重复记录-题外

发表评论

电子邮件地址不会被公开。 必填项已用*标注