sqlserver 删除重复记录处理（转）

注：此处“重复”非完全重复,意为某字段数据重复
　　hzt表结构
id　int
title　nvarchar(50)
adddate　datetime
　　数据
一. 查找重复记录
　　1. 查找全部重复记录
select * from 表 where 重复字段 in (select 重复字段 from 表 group by 重复字段 having count(*)>1)

2. 过滤重复记录(只显示一条)
select * from hzt where id in (select max(id) from hzt group by title)
注：此处显示id最大一条记录
二. 删除重复记录
　　1. 删除全部重复记录（慎用）
delete 表 where 重复字段 in (select 重复字段 from 表 group by 重复字段 having count(*)>1)

　　2. 保留一条（这个应该是大多数人所需要的）
delete hzt where id not in (select max(id) from hzt group by title)
　　注：此处保留id最大一条记录

其它相关:

删除重复记录有大小关系时,保留大或小其中一个记录

--> --> (roy)生成測試數據

if not object_id('tempdb..#t') is null
  drop table #t
go
create table #t([id] int,[name] nvarchar(1),[memo] nvarchar(2))
insert #t
select 1,n'a',n'a1' union all
select 2,n'a',n'a2' union all
select 3,n'a',n'a3' union all
select 4,n'b',n'b1' union all
select 5,n'b',n'b2'
go

--i、name相同id最小的记录(推荐用1,2,3),保留最小一条
方法1:
delete a from #t a where exists(select 1 from #t where name=a.name and id<a.id)
方法2:
delete a from #t a left join (select min(id)id,name from #t group by name) b on a.name=b.name and a.id=b.id 
where b.id is null
方法3:
delete a from #t a where id not in (select min(id) from #t where name=a.name)
方法4(注:id为唯一时可用):
delete a from #t a where id not in(select min(id)from #t group by name)
方法5:
delete a from #t a where (select count(1) from #t where name=a.name and id<a.id)>0
方法6:
delete a from #t a where id<>(select top 1 id from #t where name=a.name order by id)
方法7:
delete a from #t a where id>any(select id from #t where name=a.name)
select * from #t

生成结果:
/*
id     name memo
----------- ---- ----
1      a  a1
4      b  b1

(2 行受影响)
*/


--ii、name相同id保留最大的一条记录:
方法1:
delete a from #t a where exists(select 1 from #t where name=a.name and id>a.id)
方法2:
delete a from #t a left join (select max(id)id,name from #t group by name) b on a.name=b.name and a.id=b.id
where b.id is null
方法3:
delete a from #t a where id not in (select max(id) from #t where name=a.name)
方法4(注:id为唯一时可用):
delete a from #t a where id not in(select max(id)from #t group by name)
方法5:
delete a from #t a where (select count(1) from #t where name=a.name and id>a.id)>0
方法6:
delete a from #t a where id<>(select top 1 id from #t where name=a.name order by id desc)
方法7:
delete a from #t a where id<any(select id from #t where name=a.name)
select * from #t
/*
id     name memo
----------- ---- ----
3      a  a3
5      b  b2

(2 行受影响)
*/


--3、删除重复记录没有大小关系时，处理重复值
--> --> (roy)生成測試數據

if not object_id('tempdb..#t') is null
  drop table #t
go
create table #t([num] int,[name] nvarchar(1))
insert #t
select 1,n'a' union all
select 1,n'a' union all
select 1,n'a' union all
select 2,n'b' union all
select 2,n'b'
go

方法1:
if object_id('tempdb..#') is not null
  drop table #
select distinct * into # from #t--排除重复记录结果集生成临时表#
truncate table #t--清空表
insert #t select * from #  --把临时表#插入到表#t中

--查看结果
select * from #t

/*
num     name
----------- ----
1      a
2      b

(2 行受影响)
*/

--重新执行测试数据后用方法2
方法2:
alter table #t add id int identity--新增标识列
go
delete a from #t a where exists(select 1 from #t where num=a.num and name=a.name and id>a.id)--只保留一条记录
go
alter table #t drop column id--删除标识列

--查看结果
select * from #t

/*
num     name
----------- ----
1      a
2      b

(2 行受影响)

*/

--重新执行测试数据后用方法3
方法3:
declare roy_cursor cursor local for
select count(1)-1,num,name from #t group by num,name having count(1)>1
declare @con int,@num int,@name nvarchar(1)
open roy_cursor
fetch next from roy_cursor into @con,@num,@name
while @@fetch_status=0
begin 
  set rowcount @con;
  delete #t where num=@num and name=@name
  set rowcount 0;
  fetch next from roy_cursor into @con,@num,@name
end
close roy_cursor
deallocate roy_cursor

--查看结果
select * from #t
/*
num     name
----------- ----
1      a
2      b

(2 行受影响)
*/