将OVER（）子句和排名函数连用,就是排名窗口函数,它们只能用在SELECT子句或ORDER BY子句之后。如果放在 SELECT之后，它运行的逻顺序在 DISTINCT 之前。

逻辑处理顺序如下:

SELECT(7)<DISTINCT>(9)TOP (5)select list()<OVER0

(1)FROM...JOIN ...

(2)WHERE

(3)GROUP BY

(4)HAVING

(8)ORDER BY

它们的使用方法格式如下:

order_function OVER( [PARTITION BY expression]<ORDER BY CIase> )

order_finction 是指排名函数,包括ROW NUMBER()、RANK()、DENSE_RANK()和NTILE()与OVER()连用，OVER子句里面使用PARTITION BY关键字对输入行进行窗口划分(即分区划分)，如果OVER()子句中不写PARTITION BY，则表示对所有行进行计算，这里的所有行不是 FROM 后面表的所有行，而是经过 WHERE GROUP BY和HAVING运行之后的所有行。在PARTITION BY之后还跟上ORDER BY子句对分区内的数据进行排序，它不可以省略，否则无顺序的区内数据由于不知道排名而无法使用排名函数。

实验环境如下两表：

ROW NUMBER()进行分区编号

SELECT ROW_NUMBER() OVER(PARTITION BY employee_id ORDER BY salary) AS ID ,* FROM salary

如果没有使用PARTITION BY，就只有一个窗口

SELECT ROW_NUMBER() OVER(ORDER BY salary) AS ID ,* FROM salary

SELECT ROW_NUMBER() OVER(PARTITION BY employee_id ) AS ID ,* FROM salary

如果不适用ORDER BY 会报错，但也许此时并不希望做排序，可以使用SELECT 0 来变通

SELECT ROW_NUMBER() OVER(PARTITION BY employee_id ORDER BY (SELECT 0)) AS ID ,* FROM salary

RANK() 和DENSE_RANK()

• rank()排序相同时会重复，总数不变，即会出现1、1、3这样的排序结果；
• dense_rank()排序相同时会重复，总数会减少，即会出现1、1、2这样的排序结果；
• row_number()排序相同时不会重复，会根据顺序排序。

--更新实验环境

UPDATE salary

SET salary =60

where employee_id = 1 and salary_type ='岗位工资'

SELECT

ROW_NUMBER() OVER(PARTITION BY employee_id ORDER BY salary) AS 编号,

RANK() OVER(PARTITION BY employee_id ORDER BY salary) AS 排名,

DENSE_RANK() OVER(PARTITION BY employee_id ORDER BY salary) AS 密集排名

,* FROM salary

NTIEL()进行数据分钟

NTILE()的功能是进行“均分”分组，括号内接受一个代表要分组组数量的参数，然后以组为单位进行编号，对于组内每一行数据，NTILE 都返回此行所在组的组编号。

简单地说就是NTILE 函数将每一行数据关联到组，并为每一行分配一个所属组的编号。

假设一个表的某列值为1~10 的整数，要将这 10 行分成两组，则每个组都有5 行，表示方式为NTILE(2)。如果表某列是 1~11的整数，这11行要分成3 组的表示方式为NTILE(3)但是这时候无法“均分”，它的分配方式是先分成3 组，每组3 行数据，剩下的两行数据从前向后均分，即第一组和第二组都有 4 行数据，第三组只有3 行数据。

假设1~11的整数,分为3组（1,2,3,4）（5,6,7,8）（9,10,11）

SELECT NTILE(4) OVER(ORDER BY salary) AS 分组

,* FROM salary

学习参考资料：《跟韩老师学 SQL Server 数据库设计与开发》