分組求TOP N記錄

對資料先分組，在每組中取出TOP N記錄。[@more@]

例項：
有Chinese, math, Music三科的成績，求每科成績前3（或前2或前1）名。
drop table #tmp_score
go

create table #tmp_score
(
ScoreID int primary key,
ClassName varchar(10),
StudentName varchar(10),
Score decimal(5, 2)
)

insert into #tmp_score
select 1, 'Chinese', 'Jerry', 70
union all
select 2, 'Math', 'Jerry', 72
union all
select 3, 'Music', 'Jerry', 81
union all
select 4, 'Chinese', 'Albert', 90
union all
select 5, 'Math', 'Albert', 80
union all
select 6, 'Music', 'Albert', 85
union all
select 7, 'Chinese', 'Lewis', 95
union all
select 8, 'Math', 'Lewis', 91
union all
select 9, 'Music', 'Lewis', 88
union all
select 10, 'Chinese', 'Tom', 89
union all
select 11, 'Math', 'Tom', 83
union all
select 12, 'Music', 'Tom', 86
go

select * from #tmp_score a
where ScoreID in
(
select top 3 ScoreID from #tmp_score
where ClassName = a.ClassName
order by Score desc
)
order by ClassName asc, Score desc