sql语句如何删除重复记录？教程详解__select_from_duplicate_col_peopleId_where_

当前位置: 数据库>sqlserver

sql语句如何删除重复记录？教程详解

来源: 互联网发布时间：2014-08-29

本文导语: 本节内容： sql语句如何删除重复记录？一、sql产生重复记录的原因 1、完全重复的记录完全重复的数据，通常是由于没有设置主键/唯一键约束导致的。 2、部分重复的记录部分列重复的数据，通常表上是有主键的，...

本节内容：
sql语句如何删除重复记录？

一、sql产生重复记录的原因

1、完全重复的记录
完全重复的数据，通常是由于没有设置主键/唯一键约束导致的。

2、部分重复的记录
部分列重复的数据，通常表上是有主键的，可能是程序逻辑造成了多行数据列值的重复。

二、sql语句删除重复记录的方法

1、删除完全重复的记录
(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

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

2、删除部分重复的记录
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)

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数据库中查询及删除重复记录的方法

一、根据单个字段的重复
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 peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

比如：
在A表中存在一个字段“name”，而且不同记录之间的“name”值有可能会相同，（脚本学堂 www.）现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；

Select Name,Count(*) From A Group By Name Having Count(*) > 1

如果还查性别也相同大则如下:

Select Name,sex From A Group By Name,sex Having Count(*) > 1

二、根据多个字段的重复
tb_type_data的表结构如下所示：

其中必须根据typeId(风机类型)和speed（风速）来区分重复数据，即某种风机中，风速必须是唯一的。

1、查找表中多余的重复记录（多个字段）

select * from tb_type_data where
(typeId + speed) in
(select (typeId + speed) from tb_type_data group by typeId , speed having count(*) > 1)

2.删除表中多余的重复记录（多个字段）

delete from tb_type_data where
TYPEid + speed in
(select TYPEid + speed from tb_type_data group by TYPEid+speed having count(*) > 1)
and dataid not in(
select min(dataId) from tb_type_data group by TYPEid,speed having count(*) > 1)

以下的方法本人没有试验过，先记录在此：
方法一：

declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >； 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from 表名 where 主字段 = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0

方法二
有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。
1、对于第一种重复，比较容易解决，使用

select distinct * from tableName

就可以得到无重复记录的结果集。

如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除

select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp

发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。

2、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下
假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集

select identity(int,1,1) as autoID, * into #Tmp from tableName
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2)

最后一个select即得到了Name，Address不重复的结果集（但多了一个autoID字段，实际写时可以写在select子句中省去此列）。