分组求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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/66009/viewspace-1012153/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/66009/viewspace-1012153/