假设有一张表tbA(a, b, c)数据如下:
a b c
------ ------ ------
aaa xxxx sdfas
ppp skdjf as
g kjasl kdkdk
q hhhhh 63423
aaa 11111 11111
b kkkkk 56464
g yyyyy yyyywo
x yiowu 558868655现在我想得到如下数据:a b c
------ ------ ------
aaa xxxx sdfas
ppp skdjf as
g kjasl kdkdk
q hhhhh 63423
被删除的行
b kkkkk 56464
被删除的行
x yiowu 558868655就是说当a字段的数据重复时只取出现在最前面的那条记录
a b c
------ ------ ------
aaa xxxx sdfas
ppp skdjf as
g kjasl kdkdk
q hhhhh 63423
aaa 11111 11111
b kkkkk 56464
g yyyyy yyyywo
x yiowu 558868655现在我想得到如下数据:a b c
------ ------ ------
aaa xxxx sdfas
ppp skdjf as
g kjasl kdkdk
q hhhhh 63423
被删除的行
b kkkkk 56464
被删除的行
x yiowu 558868655就是说当a字段的数据重复时只取出现在最前面的那条记录
from tbaselect *
from # ta
where not exists(select 1 from # where ta.a = a and px < ta.px)drop table #
insert @t select
'aaa','xxxx ','sdfas ' insert @t select
'ppp ','skdjf ','as 'insert @t select
'g ','kjasl ','kdkdk ' insert @t select
'q ','hhhhh ','63423 ' insert @t select
'aaa ','11111 ','11111 ' insert @t select
'b ','kkkkk ','56464 ' insert @t select
'g ','yyyyy ','yyyywo ' insert @t select
'x ','yiowu ','558868655 'select *,px = identity(int,1,1) into #
from @t
select * from #
select *
from # ta
where not exists(select 1 from # where ta.a = a and px < ta.px)drop table #/*
a b c px
-------------------- -------------------- -------------------- -----------
aaa xxxx sdfas 1
ppp skdjf as 2
g kjasl kdkdk 3
q hhhhh 63423 4
aaa 11111 11111 5
b kkkkk 56464 6
g yyyyy yyyywo 7
x yiowu 558868655 8a b c px
-------------------- -------------------- -------------------- -----------
aaa xxxx sdfas 1
ppp skdjf as 2
g kjasl kdkdk 3
q hhhhh 63423 4
b kkkkk 56464 6
x yiowu 558868655 8
*/
--注:谨慎参考,未经过广泛测试
declare @t table(a varchar(20),b varchar(20),c varchar(20))
insert @t select
'aaa','xxxx ','sdfas ' insert @t select
'ppp ','skdjf ','as 'insert @t select
'g ','kjasl ','kdkdk ' insert @t select
'q ','hhhhh ','63423 ' insert @t select
'aaa ','11111 ','11111 ' insert @t select
'b ','kkkkk ','56464 ' insert @t select
'g ','yyyyy ','yyyywo ' insert @t select
'x ','yiowu ','558868655 '
select distinct a.a,(select top 1 b from @t as b where b.a=a.a) as b,(select top 1 c from @t as c where c.a=a.a) as c from @t as a
insert @t select
'aaa','xxxx ','sdfas ' insert @t select
'ppp ','skdjf ','as 'insert @t select
'g ','kjasl ','kdkdk ' insert @t select
'q ','hhhhh ','63423 ' insert @t select
'aaa ','11111 ','11111 ' insert @t select
'b ','kkkkk ','56464 ' insert @t select
'g ','yyyyy ','yyyywo ' insert @t select
'x ','yiowu ','558868655 'select
*
from
@T t
where
checksum(b,c)=(select top 1 checksum(b,c)from @t where a=t.a)
a b c
-------------------- -------------------- --------------------
aaa xxxx sdfas
ppp skdjf as
g kjasl kdkdk
q hhhhh 63423
b kkkkk 56464
x yiowu 558868655 (所影响的行数为 6 行)
select
*
from
@T t
where
checksum(b,c)=(select top 1 checksum(b,c)from @t where a=t.a)
这样做可能会有一个风险,如果子查询用到索引的话,那么派出来的顺序就不可预测了,做好能有order by这样的限制
delete tba from tba t where b not in (select min(b) from tba where a = t.a)
insert into tba values('aaa' ,'xxxx ' ,'sdfas ' )
insert into tba values('ppp' ,'skdjf ','as ')
insert into tba values('g' ,'kjasl ','kdkdk ' )
insert into tba values('q' ,'hhhhh ','63423 ' )
insert into tba values('aaa ','11111 ','11111 ' )
insert into tba values('b ' ,'kkkkk ','56464 ' )
insert into tba values('g ' ,'yyyyy ','yyyywo ' )
insert into tba values('x ' ,'yiowu ' ,'558868655 ')
godelete tba from tba t where b not in (select max(b) from tba where a = t.a)select * from tbadrop table tba/*
a b c
-------------------- -------------------- --------------------
aaa xxxx sdfas
ppp skdjf as
q hhhhh 63423
b kkkkk 56464
g yyyyy yyyywo
x yiowu 558868655 (所影响的行数为 6 行)
*/create table tba(a varchar(20),b varchar(20),c varchar(20))
insert into tba values('aaa' ,'xxxx ' ,'sdfas ' )
insert into tba values('ppp' ,'skdjf ','as ')
insert into tba values('g' ,'kjasl ','kdkdk ' )
insert into tba values('q' ,'hhhhh ','63423 ' )
insert into tba values('aaa ','11111 ','11111 ' )
insert into tba values('b ' ,'kkkkk ','56464 ' )
insert into tba values('g ' ,'yyyyy ','yyyywo ' )
insert into tba values('x ' ,'yiowu ' ,'558868655 ')
godelete tba from tba t where b not in (select min(b) from tba where a = t.a)select * from tbadrop table tba/*
a b c
-------------------- -------------------- --------------------
ppp skdjf as
g kjasl kdkdk
q hhhhh 63423
aaa 11111 11111
b kkkkk 56464
x yiowu 558868655 (所影响的行数为 6 行)
*/
select px = identity(int,1,1),* into # from tbaselect * from # a
where not exists(select 1 from # where a.a = a and px < a.px)