if object_id('[tb]') is not null drop table [tb] go create table [tb]([A] int,[B] int) insert [tb] select 1,1 union all select 1,2 union all select 2,3 union all select 2,3 union all select 2,4 union all select 3,5 union all select 3,5 union all select 3,5 union all select 3,6 go;with cte as (select rn=row_number() over(order by getdate()),* from tb)select a.a,a.b from cte a join (select min(rn) as rn,a,b from cte group by a,b) b on a.a=b.a and a.b=b.b and a.rn!=b.rn/** a b ----------- ----------- 2 3 3 5 3 5(3 行受影响) **/
http://blog.csdn.net/yangsh0722/article/details/8021246
go
create table [tb]([A] int,[B] int)
insert [tb]
select 1,1 union all
select 1,2 union all
select 2,3 union all
select 2,3 union all
select 2,4 union all
select 3,5 union all
select 3,5 union all
select 3,5 union all
select 3,6
go;with cte as
(select rn=row_number() over(order by getdate()),* from tb)select a.a,a.b
from cte a
join (select min(rn) as rn,a,b from cte group by a,b) b
on a.a=b.a and a.b=b.b and a.rn!=b.rn/**
a b
----------- -----------
2 3
3 5
3 5(3 行受影响)
**/