-->生成测试数据
declare @tb table([a] int,[b] nvarchar(1),[c] nvarchar(1))
Insert @tb
select 1,N'x',N'x' union all
select 1,N'x',N'x' union all
select 1,N'x',N'x' union all
select 1,N'x',N'x' union all
select 2,N'x',N'x' union all
select 2,N'x',N'x' union all
select 2,N'x',N'x' union all
select 2,N'x',N'x' union all
select 3,N'x',N'x' union all
select 3,N'x',N'x' union all
select 3,N'x',N'x' union all
select 3,N'x',N'x' union all
select 4,N'x',N'x'
select * from (
Select row_number() over(partition by [a] order by [a]) as num, * from @tb
)a where a.num <=2
/*
--2005
num a b c
-------------------- ----------- ---- ----
1 1 x x
2 1 x x
1 2 x x
2 2 x x
1 3 x x
2 3 x x
1 4 x x
*/
declare @tb table([a] int,[b] nvarchar(1),[c] nvarchar(1))
Insert @tb
select 1,N'x',N'x' union all
select 1,N'x',N'x' union all
select 1,N'x',N'x' union all
select 1,N'x',N'x' union all
select 2,N'x',N'x' union all
select 2,N'x',N'x' union all
select 2,N'x',N'x' union all
select 2,N'x',N'x' union all
select 3,N'x',N'x' union all
select 3,N'x',N'x' union all
select 3,N'x',N'x' union all
select 3,N'x',N'x' union all
select 4,N'x',N'x'
select * from (
Select row_number() over(partition by [a] order by [a]) as num, * from @tb
)a where a.num <=2
/*
--2005
num a b c
-------------------- ----------- ---- ----
1 1 x x
2 1 x x
1 2 x x
2 2 x x
1 3 x x
2 3 x x
1 4 x x
*/
Select row_number() over(partition by [a] order by [a]) as num, * from @tb where [a]<4
)a where a.num <=2
/*
--2005
num a b c
-------------------- ----------- ---- ----
1 1 x x
2 1 x x
1 2 x x
2 2 x x
1 3 x x
2 3 x x
*/
If object_id('ta') is not null
Drop table ta
Go
Create table ta(a int,b varchar(1),c varchar(1))
Go
Insert into ta
select 1,'x','x' union all
select 1,'x','x' union all
select 1,'x','x' union all
select 1,'x','x' union all
select 2,'x','x' union all
select 2,'x','x' union all
select 2,'x','x' union all
select 2,'x','x' union all
select 3,'x','x' union all
select 3,'x','x' union all
select 3,'x','x' union all
select 3,'x','x' union all
select 4,'x','x'
Go
--Start
select px = identity(int,1,1),* into #
from taSelect a.a,a.b,a.c
from # a left join # b
on a.a = b.a and a.b = b.b
group by a.px,a.a,a.b,a.c
having sum(case when a.px > b.px then 1 else 0 end) < 2drop table #
--Result:
/*a b c
----------- ---- ----
1 x x
1 x x
2 x x
2 x x
3 x x
3 x x
4 x x(所影响的行数为 7 行)*/
--End