By

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的功能.

原文链接: http://www.2cto.com/database/201212/180307.html
参考链接: http://www.2cto.com/database/201212/180278.html