--保留name相同的纪录中id最小的delete t1 from tab1 t1 where exists (select 1 from tab1 where name = t1.name and id < t1.id)
1、全部删除 delete from tb where name not in (select name from tb group by name having count(*) > 1) 2、保留一条(假设保留ID最小的) delete from tb where not exists (select name , min(id) as id from tb group by name)
也可以利用临时表来处理 select distanct * into #t from tab1 把数据拷贝到临时表 delete from tab1 删除数据 select * into tab1 把临时表的数据拷贝回来 drop #t 删除临时表
coolingpipe(冷箫轻笛) ( ) 信誉:100 Blog 好快回答!这里的哥们真热心呀,让我想起一首老歌,这里的人民爱和平,。。带领我们成长,谢谢了!
declare @t table(id varchar(3),name varchar(3)) insert @t select '01', 'A' union all select '02', 'A' union all select '03' , 'B' union all select '04' , 'C' union all select '05' , 'B' union all select '06' , 'D'delete t from @t t where id=(select top 1 id from @t where name=t.name)and(select count(*) from @t where name=t.name)>1 select * from @t(所影响的行数为 6 行) (所影响的行数为 2 行)id name ---- ---- 02 A 04 C 05 B 06 D(所影响的行数为 4 行)
改一下 delete t from @t t where id>(select top 1 id from @t where name=t.name order by id asc)
delete table where not exists (Select name,min(id) from table group by name)
where exists (select 1 from tab1 where name = t1.name and id < t1.id)
delete from tb where name not in (select name from tb group by name having count(*) > 1)
2、保留一条(假设保留ID最小的)
delete from tb where not exists (select name , min(id) as id from tb group by name)
问一下一楼的,是否可以这样理解,t1是tab1派生的一个表,表结构和数据和原表tab1完全相同,这是个自身连接,用表减表,取交集?我不懂这样做的原理是什么,帮忙答复一下吧!
=================================
正解
就是用了自联接
对于表t1(实际上就是tab1),你一条纪录一条纪录的来看试试,就很容易理解了
delete from tab1 删除数据 select * into tab1 把临时表的数据拷贝回来
drop #t 删除临时表
insert @t select
'01', 'A'
union all select
'02', 'A'
union all select
'03' , 'B'
union all select
'04' , 'C'
union all select
'05' , 'B'
union all select
'06' , 'D'delete t from @t t where id=(select top 1 id from @t where name=t.name)and(select count(*) from @t where name=t.name)>1
select * from @t(所影响的行数为 6 行)
(所影响的行数为 2 行)id name
---- ----
02 A
04 C
05 B
06 D(所影响的行数为 4 行)
delete t from @t t where id>(select top 1 id from @t where name=t.name order by id asc)