表a
groupnum B
1 11
1 11
1 10
2 11
2 20
2 20
2 11
2 15
3 23
3 23
3 15
3 6
3 6
3 6
...
...删除每组(groupnum)里有重复的行只保留一条
groupnum B
1 11
1 11
1 10
2 11
2 20
2 20
2 11
2 15
3 23
3 23
3 15
3 6
3 6
3 6
...
...删除每组(groupnum)里有重复的行只保留一条
--sql2000等高手来,我来个一通俗的
select distinct * into #tmp from a
delete from a
insert into a select * from #tmp
drop table #tmp
--sql 2005
delete from a
from (select row_number() over(partition by groupnum,B order by groupnum,B ) n,* from a) as a
where n!=1
insert into a select groupnum , B from tmpdrop table tmp
drop table [表a]
go
create table [表a](groupnum int,B int)
INSERT INTO [表a] VALUES(1,11)
INSERT INTO [表a] VALUES(1,11)
INSERT INTO [表a] VALUES(1,10)
INSERT INTO [表a] VALUES(2,11)
INSERT INTO [表a] VALUES(2,20)
INSERT INTO [表a] VALUES(2,20)
INSERT INTO [表a] VALUES(2,11)
INSERT INTO [表a] VALUES(2,15)INSERT INTO [表a] VALUES(3,23)
INSERT INTO [表a] VALUES(3,23)
INSERT INTO [表a] VALUES(3,15)
INSERT INTO [表a] VALUES(3,6)
INSERT INTO [表a] VALUES(3,6)
INSERT INTO [表a] VALUES(3,6)
;with cte
as
(
select * ,rn=ROW_NUMBER()over(partition by groupnum,B order by groupnum) from 表a
)
delete cte where rn<>1select * from [表a]
groupnum B
----------- -----------
1 11
1 10
2 11
2 20
2 15
3 23
3 15
3 6(8 行受影响)