删除重复行

 

if OBJECT_ID('表a') is not null
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<>1

select * from [表a]


groupnum    B
----------- -----------
1           11
1           10
2           11
2           20
2           15
3           23
3           15
3           6

(8 行受影响)