本文为原创，如需转载，请注明作者和出处，谢谢！
假设有一个表，SQL语句如下：
CREATE
TABLE
[
dbo
].
[
scan
](
[ km ] [ int ] NULL,
[ kh ] [ int ] NULL,
[ cj ] [ int ] NULL
) ON [ PRIMARY ]
[ km ] [ int ] NULL,
[ kh ] [ int ] NULL,
[ cj ] [ int ] NULL
) ON [ PRIMARY ]
其中km为科目号、kh为考生号、cj为成绩，现对km和kh进行分组，并获得每组前2条记录（按cj从高到低排序）。基本思想是为每组加一个序号列，再用where取序号小于等于2的。SQL语句如下：
select
*
from
(
select a.km,a.kh,cj,row_number() over(partition by a.km order by a.km,a.cj desc) n
from
( select km,kh, SUM(cj) cj from scan group by km,kh) a
) b where n <= 2 order by km, cj desc
(
select a.km,a.kh,cj,row_number() over(partition by a.km order by a.km,a.cj desc) n
from
( select km,kh, SUM(cj) cj from scan group by km,kh) a
) b where n <= 2 order by km, cj desc
最后得到的结果集如下图所示。
本文转自银河使者博客园博客，原文链接http://www.cnblogs.com/nokiaguy/archive/2009/11/03/1594919.html如需转载请自行联系原作者
银河使者
