sqlserver 删除重复记录处理（转）发布：mdxy-dxy 字体：[增加 减小 ] 类型：转载删除重复记录有大小关系时,保留大或小其中一个记录注：此处“重复”非完全重复,意为某字段数据重复 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 #tgocreate table #t([id] int,[name] nvarchar(1),[memo] nvarchar(2))insert #tselect 1,n'a',n'a1' union allselect 2,n'a',n'a2' union allselect 3,n'a',n'a3' union allselect 4,n'b',n'b1' union allselect 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 ida.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 ida.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 a14 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.idwhere 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(select id from #t where name=a.name)select * from #t/*id name memo----------- ---- ----3 a a35 b b2(2 行受影响)*/--3、删除重复记录没有大小关系时，处理重复值--> --> (roy)生成測試數據if not object_id('tempdb..#t') is null drop table #tgocreate table #t([num] int,[name] nvarchar(1))insert #tselect 1,n'a' union allselect 1,n'a' union allselect 1,n'a' union allselect 2,n'b' union allselect 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 a2 b(2 行受影响)*/--重新执行测试数据后用方法2方法2:alter table #t add id int identity--新增标识列godelete a from #t a where exists(select 1 from #t where num=a.num and name=a.name and id>a.id)--只保留一条记录goalter table #t drop column id--删除标识列--查看结果select * from #t/*num name----------- ----1 a2 b(2 行受影响)*/--重新执行测试数据后用方法3方法3:declare roy_cursor cursor local forselect count(1)-1,num,name from #t group by num,name having count(1)>1declare @con int,@num int,@name nvarchar(1)open roy_cursorfetch next from roy_cursor into @con,@num,@namewhile @@fetch_status=0begin set rowcount @con; delete #t where num=@num and name=@name set rowcount 0; fetch next from roy_cursor into @con,@num,@nameendclose roy_cursordeallocate roy_cursor--查看结果select * from #t/*num name----------- ----1 a2 b(2 行受影响)*/