这是在做一个考试成绩统计时遇到的问题。假设有如表1所示的数据，其中包含了3个班级的考生成绩，如果是希望获取全部数据的前2名，可以使用TOP配合ORDER BY子句轻易实现，但是如果我们希望取出每个班级中的前2名呢？事情就不这么简单了。
SELECT TOP(2) *
FROM Students
ORDER BY Achi DESC;
表1 考试成绩表
| ClassID |
StuName |
Achi |
| 1 |
张山 |
100 |
| 1 |
李明 |
90 |
| 1 |
王磊 |
95 |
| 2 |
孙科 |
100 |
| 2 |
赵强 |
80 |
| 2 |
王智 |
90 |
| 3 |
李海 |
95 |
下面的语句用于创建示例表：
CREATE TABLE Students
(ClassID int,
StuNamechar(10),
Achi int);
INSERT INTO Students
VALUES(1, '张山', 100),
(1, '李明', 90),
(1, '王磊', 95),
(2, '孙科', 100),
(2, '赵强', 80),
(2, '王智', 90),
(3, '李海', 95);
1.使用联接获取前几行
如果将Students表打开两次，将一个考生与其大于或等于自己成绩的考生联接，我们看看会得到什么样的结果。参考下面的语句：
SELECT S1.*, S2.*
FROM Students AS S1
INNER JOINStudents AS S2
ONS1.ClassID = S2.ClassID
ANDS2.Achi >= S1.Achi
ORDER BY S1.ClassID, S1.Achi DESC;
结果如表2所示。
表2 联接结果
| S1.ClassID |
S1.StuName |
S1.Achi |
S2.ClassID |
S2.StuName |
S2.Achi |
| 1 |
张山 |
100 |
1 |
张山 |
100 |
| 1 |
王磊 |
95 |
1 |
张山 |
100 |
| 1 |
王磊 |
95 |
1 |
王磊 |
95 |
| 1 |
李明 |
90 |
1 |
张山 |
100 |
| 1 |
李明 |
90 |
1 |
李明 |
90 |
| 1 |
李明 |
90 |
1 |
王磊 |
95 |
| 2 |
孙科 |
100 |
2 |
孙科 |
100 |
| 2 |
王智 |
90 |
2 |
孙科 |
100 |
| 2 |
王智 |
90 |
2 |
王智 |
90 |
| 2 |
赵强 |
80 |
2 |
孙科 |
100 |
| 2 |
赵强 |
80 |
2 |
赵强 |
80 |
| 2 |
赵强 |
80 |
2 |
王智 |
90 |
| 3 |
李海 |
95 |
3 |
李海 |
95 |
从上表中可以看出，1班中的第1名张山有1条记录，第2名王磊有2条记录，第3名有3条记录。因此，我们可以使用下面的语句来获取每班中前2名的考生。查询结果如表3所示。
SELECT S1.ClassID, S1.Achi, MAX(S1.StuName) ASStuName
FROM Students AS S1
INNER JOINStudents AS S2
ONS1.ClassID = S2.ClassID
ANDS2.Achi >= S1.Achi
GROUP BY S1.ClassID, S1.Achi
HAVING COUNT(*) <=2
ORDER BY S1.ClassID, S1.Achi DESC;
表3 每班中前2名的考生
| ClassID |
Achi |
StuName |
| 1 |
100 |
张山 |
| 1 |
95 |
王磊 |
| 2 |
100 |
孙科 |
| 2 |
90 |
王智 |
| 3 |
95 |
李海 |
2.使用窗口排名函数获取前几行
窗口计算是从SQLServer 2005开始提供的新技术，每一组数据被称为一个窗口，RANK( )和DENSE_RANK( )函数都可以按窗口进行排名计算，表4描述了这两种排名方式的差异。
表4 RANK( )和DENSE_RANK( )函数排名的差异
| StuName |
Achi |
RANK( )排名 |
DENSE_RANK( )排名 |
| 张三 |
100 |
1 |
1 |
| 李四 |
100 |
1 |
1 |
| 王五 |
95 |
3 |
2 |
| 赵六 |
90 |
4 |
3 |
从表中可以看出，无论是RANK()还是DENSE_RANK()，相同的考试成绩排名值是相同的，张三和李四都是第1，也就是我们常说的并列第1。但是，两人之后的名次RANK( )和DENSE_RANK( )出现了差异，从两人并列第1的角度讲，他们两人之后的名次应当是第2，这是DENSE_RANK( )函数的排名方式，也称为密集排名，因为它的名次之间没有间隔；前面已经有2个人100分了，他们后面的人应当是第3个高分者，从这个角度理解，后面的名次应当是第3，这是RANK( )的排名方式。
例如，下面的语句按班级分组、按成绩降序密集排名，查询结果如表5所示。与上面使用联接获取前2名的方式相比，使用排名函数可以正确处理成绩并列现象。同样是获取成绩前2名，存在成绩并列时，使用排名函数从每个班级中取出的人数有可能超过2个。
SELECT ClassID, StuName, Achi,
DENSE_RANK() OVER(PARTITION BY ClassID ORDERBY Achi DESC) AS rank_rn
FROM Students;
表5 排序结果
| ClassID |
StuName |
Achi |
rank_rn |
| 1 |
张山 |
100 |
1 |
| 1 |
王磊 |
95 |
2 |
| 1 |
李明 |
90 |
3 |
| 2 |
孙科 |
100 |
1 |
| 2 |
王智 |
90 |
2 |
| 2 |
赵强 |
80 |
3 |
| 3 |
李海 |
95 |
1 |
从上表可以看出，我们只要取出rank_rn小于或等于2的考生即可。下面是完整的查询语句，在性能方面，该语句要高于联接方式。
WITH StuRank(ClassID, StuName, Achi, rank_rn)
AS
(SELECT ClassID, StuName, Achi,
DENSE_RANK() OVER(PARTITION BY ClassID ORDER BY Achi DESC)
FROMStudents
)SELECT * FROMStuRank WHERE rank_rn <= 2;