SQL 取分组中的前几名

create table #aa(class varchar(10),name varchar(10),lang int,math int)
go

insert into #aa
select '1','a1',60,70
union all
select '1','a2',80,70
union all
select '1','a3',90,70
union all
select '1','a4',50,70
go

insert into #aa
select '2','b1',60,70
union all
select '2','b2',90,90
union all
select '2','b3',90,70
union all
select '2','b4',50,70

go

select * from #aa

--取每年级前一名的成级
select * from
    (select ROW_NUMBER() over(partition by class order by lang+math desc) rr, * from #aa ) a
where     rr<2
--取每年级前二名的成级
select * from
    (select ROW_NUMBER() over(partition by class order by lang+math desc) rr, * from #aa ) a
where     rr<3