SQL SERVER OVER开窗函数，Partition By,ROW_NUMBER(),DENSE_RANK(),RANK()排名函数

OVER 子句定义查询结果集内的窗口或用户指定的行集

Partition  By用于给结果集分组，在OVER子句中使用

1.OVER()函数可配合排名函数（ROW_NUMBER,DENSE_RANK,RANK)产生排名结果集

2.OVER()函数可配合聚合函数，使每个行集得到统计列

 

示例用法如下

--DROP TABLE #Student
CREATE TABLE #Student  --学生语文成绩表
(
 id INT,  --主键
 Student VARCHAR(20),--学号
 Class VARCHAR(20), --班级
 Score INT --分数
)
GO

INSERT #Student 
 SELECT 1,'S01','一班',88
 UNION ALL SELECT  2,'S02','一班',66
 UNION ALL SELECT  3,'S03','一班',75
 UNION ALL SELECT  4,'S05','二班',30
 UNION ALL SELECT  5,'S06','二班',70
 UNION ALL SELECT  6,'S07','二班',80
 UNION ALL SELECT  7,'S08','二班',70
 UNION ALL SELECT  8,'S09','三班',90
 UNION ALL SELECT  9,'S010','三班',70
 UNION ALL SELECT  10,'S011','三班',80

GO

SELECT * FROM #Student

--分数总排名，不考虑并列情况
SELECT * ,ROW_NUMBER() OVER(ORDER BY Score DESC) AS 总排名,COUNT(*) OVER() AS 总人数 FROM #Student

--分数总排名，分数相同的做为并列排名，排名连续
SELECT *,DENSE_RANK() OVER (ORDER BY Score DESC) AS 总排名 FROM #Student

--分数总排名，分数相同的做为并列排名，排名不连续，如两个并列第二名，则下一个第四名（注意不是第三名）
SELECT *,RANK() OVER(ORDER BY Score DESC) AS 总排名 FROM #Student


--各班级分数排名，PARTITION BY对班级进行分组，然后再进行排名
SELECT * ,DENSE_RANK()OVER(PARTITION BY Class ORDER BY Score DESC) AS 各班级排名 FROM #Student

--使用OVER开窗函数与聚合配合，行集得到全部班级的总人数
SELECT *,COUNT(id) OVER()AS 总人数 FROM #Student


--使用OVER开窗函数与聚合配合，行集得到每个班级的各自总人数
SELECT *,COUNT(id) OVER(PARTITION BY Class)AS 总人数 FROM #Student