获取某数据前n行

declare @t table(id int,class int,marks int)
insert @t select 1,1,100
union all select 2,1,92
union all select 3,1,99
union all select 4,1,90
union all select 5,1,99
union all select 6,2,97
union all select 7,3,85
union all select 8,2,90

 

select t.* from @t t where id in(select top 3 id from @t where class=t.class order by marks desc) order by class,marks desc

 

 

select * from @t t where (select count(1)+1 from @t where class=t.class and marks>t.marks)<=3

 

 

select * from (select *,row_number() over(partition by class order by marks desc)as px from tb) where px<4