前言:
而今我们对“sql中排名函数”大约比较关注,看官们都需要学习一些“sql中排名函数”的相关资讯。那么小编同时在网上汇集了一些有关“sql中排名函数””的相关内容,希望我们能喜欢,你们快快来学习一下吧!将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 数据库设计与开发》
标签: #sql中排名函数