row_number和partition by分组取top数据，每组查询前N条

--取每个学科的前3条数据

select * from
(
select subject,name,score,ROW_NUMBER() over(PARTITION by subject order by @@identity) as num from #score
) T where T.num <= 3 order by subject