MSSQL每个分组取前条记录

--CREATE TABLE Students
--(
--    ClassID int,
--    StuName char(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 JOIN Students AS S2
  ON S1.ClassID = S2.ClassID AND S2.Achi >= S1.Achi
ORDER BY S1.ClassID, S1.Achi DESC;

--从上表中可以看出，1班中的第1名张山有1条记录，第2名王磊有2条记录，第3名有3条记录。

--获取每班中前2名的考生
SELECT S1.ClassID, S1.Achi, MAX(S1.StuName) AS StuName
FROM Students AS S1
INNER JOIN Students AS S2
  ON S1.ClassID = S2.ClassID AND S2.Achi >= S1.Achi
GROUP BY S1.ClassID, S1.Achi 
HAVING COUNT(*) <=2
ORDER BY S1.ClassID, S1.Achi DESC;

--使用窗口排名函数获取前几行
--窗口计算是从SQLServer 2005开始提供的新技术，
--每一组数据被称为一个窗口，
--RANK( )和DENSE_RANK( )函数都可以按窗口进行排名计算
--Rank()        排名名次间会存在间隔
--DENSE_Rank()  密集排名,名次之间不会有间隔
SELECT ClassID
     , StuName
     , Achi
     , RANK() OVER(PARTITION BY ClassID ORDER BY Achi DESC) AS rank_r
     , DENSE_RANK() OVER(PARTITION BY ClassID ORDER BY Achi DESC) AS rank_rn
FROM Students;

--每班取前两名
WITH StuRank(ClassID, StuName, Achi, rank_rn)AS(
SELECT ClassID
     , StuName
     , Achi
     , DENSE_RANK() OVER(PARTITION BY ClassID ORDER BY Achi DESC) rank_rn
FROM Students
)SELECT * FROM StuRank WHERE rank_rn <= 2;

也可以直接使用子查询实现上面的CTE的功能.

原文链接:
参考链接: