【sql】— SQL Server 中的排名函数

前言

     小编在项目中经常用到Row_Number()函数，它是sql server 2012 出现的四大排名函数(排名函数为分区中的每一行返回一个排名值)之一，下面为大家介绍一下它的用法。

正文

     使用到的表原始数据 (SELECT ROW_NUMBER() OVER (ORDER BY PK)AS Number ,PK,OrderID,BOPID,StepQueue FROM dbo.PLM_BOP_PPR WHERE OrderID = ‘100000003921-03’)

1、Row_Number()

     返回结果集分区内行的序列号，每个分区的第一行从1开始，也需要专门一列用于存放排序后的编号。
语法

ROW_NUMBER ( )   
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )  

示例

-- 根据PK 的顺序为当前表中记录产生一个顺序列 
SELECT  ROW_NUMBER() OVER (ORDER BY PK)AS Number ,PK,OrderID,BOPID,StepQueue FROM dbo.PLM_BOP_PPR WHERE OrderID = '100000003921-03'

适用情况 ： web分页查询显示行号、一些需要排序信息的应用中。

2、Rank()

    Rank函数用于返回结果集的分区内每行的排名， 行的排名是相关行之前的排名数加一。与Row_Nmber()函数类似，不同的是Rank()是跳跃排序。
    Rank函数考虑到了over子句中排序字段值相同的情况，如果使用rank函数来生成序号，over子句中排序字段值相同的序号是一样的，后面不同的字段的序号将跳过相同的序号排下一个，具体看例子吧。
语法

RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )  

示例

-- 按照 BOPID 来进行排名 
-- BOPID 相同的数据排名是一样的，后面的排名根据当前的记录数以此类推
SELECT  RANK()OVER (ORDER BY BOPID)AS Rank ,PK,OrderID,BOPID,StepQueue FROM dbo.PLM_BOP_PPR WHERE OrderID = '100000003921-03'

3、Dense_Rank()

    dense_rank函数的功能与rank函数类似，rank函数在生成序列号时是跳跃的，而dense_rank函数是连续的。也就是说，当遇到相同排名时，将紧接着下一次的排名值增加。
语法

DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )

示例

SELECT  DENSE_RANK()OVER (ORDER BY BOPID)AS Rank ,PK,OrderID,BOPID,StepQueue FROM dbo.PLM_BOP_PPR WHERE OrderID = '100000003921-03'

一点小想法：Rank()函数和Dense_Rank()函数可以用考试时的例子来理解，考试结束后，如果几个同学考的分数一样高老师发奖状的时候会给他们发一样名次的奖状，A、B、C同学并列第一名，再低一点的分数是第二名、第三名，这便是Dense_Rank()的体现；但是在那全班同学的成绩单上往往会根据学生姓名排名，即使分数一样，也会显示1、2、3名，成绩第二名的同学在成绩单上就体现为第四名，这便是Rank()函数的体现。

4、Ntile()

将有序分区中的行分发到指定数目的组中。 各个组有编号，编号从一开始。 对于每一个行，NTILE 将返回此行所属的组的编号。
语法

NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )
integer_expression   --指定组数

示例

SELECT  NTILE(3) OVER (ORDER BY BOPID)AS Ntile ,PK,OrderID,BOPID,StepQueue FROM dbo.PLM_BOP_PPR WHERE OrderID = '100000003921-03'

分了3组，17 = 6 + 6 + 5

【分组约定】
    1、每组的记录数不能大于它上一组的记录数，即编号小的组放的记录数不能小于编号大的组。也就是说，第1组中的记录数只能大于等于第2组及以后各组中的记录数。
    2、所有组中的记录数要么都相同，要么从某一个记录较少的组（命名为X）开始后面所有组的记录数都与该组（X组）的记录数相同。也就是说，如果有个组，前三组的记录数都是9，而第四组的记录数是8，那么第五组和第六组的记录数也必须是8。

使用排名函数注意事项

• 排名函数必须有包含 ORDER BY 的 OVER 子句
• 分组内从1开始排序
• 在使用over等函数时，over里头的分组及排序的执行晚于“where，group by，order by”的执行

总结

    个人觉得排名函数中用的比较多的是Row_Number函数，当然了不同业务场景下还是要选适合的，今天的排名函数先介绍到这，期待下次再会~~