sql 四大排序（ROW_NUMBER,RANK,DENSE_RANK,NTILE）

一.ROW_NUMBER

　ROW_NUMBER一般可以用来实现web程序的分页，他会为查询出来的每一行记录生成一个序号，依次排序且不会重复，注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号

   ROW_NUMBER() 函数语法:

ROW_NUMBER() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
) 

在这个语法中：

• 首先，PARTITION BY子句将结果集划分为分区。ROW_NUM()功能在分区内执行，并在跨越分区边界时重新初始化。(可以不填写)
• 其次，ORDER BY子句按一个或多个列或表达式对分区内的行进行排序。

示例如下：

SELECT name,age,score,"row_number"() OVER(ORDER BY score desc) as row_num FROM tt

"row_num"列就是row_number()函数生成的序号列，其原理就是使用over字句中的排序语句对记录进行排序，然后按照顺序生成序号。

注：over子句中的order by子句与sql语句中的order by 子句没有任何关系，这两处order by的字段可以不相同，如下所示

SELECT name,age,score,"row_number"() OVER(ORDER BY score desc) as row_num FROM tt ORDER BY age

注：如果使用row_number()实现分页时，需要将over子句中的order by 字段与sql排序记录中的order by 字段保持一致，否则就会出现 上图中row_num不连续的情况

二.RANK

rank()函数返回结果集分区内指定字段的值的排名，指定字段的值的排名是相关行之前的排名加一（如果有相同字段值，那么排名就会出现间隔），rank()函数的使用方法与row_number()函数完全相同。

RANK() 函数语法:

RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
) 

在这个语法中：

• 首先，PARTITION BY子句将结果集划分为分区。RANK()功能在分区内执行，并在跨越分区边界时重新初始化。(可以不填写)
• 其次，ORDER BY子句按一个或多个列或表达式对分区内的行进行排序。

示例如下：

SELECT name,age,score,"rank"() OVER(ORDER BY score desc) as row_num FROM tt 

三.DENSE_RANK

DENSE_RANK()函数返回结果集分区内指定字段的值的排名，指定字段的值的排名是连续的没有间隔，dense_rank()函数的使用方法与rank()函数完全相同。

DENSE_RANK() 函数语法:

DENSE_RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
) 

在这个语法中：

• 首先，PARTITION BY子句将结果集划分为分区。DENSE_RANK()功能在分区内执行，并在跨越分区边界时重新初始化。(可以不填写)
• 其次，ORDER BY子句按一个或多个列或表达式对分区内的行进行排序。

示例如下：

SELECT name,age,score,"dense_rank"() OVER(ORDER BY score desc) as row_num FROM tt 

四.NTILE

NTILE()函数将排序分区中的行划分为特定数量的组。从每个组分配一个从一开始的桶号。对于每一行，NTILE()函数返回一个桶号，表示行所属的组。

NTILE()函数语法：

NTILE(n) OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
) 

在这个语法中：

•  n是一个字面正整数。桶号的范围是1到n。
• 在PARTITION BY从返回的结果集划分FROM子句为分区到的NTILE()函数被应用。
• ORDER BY子句指定将NTILE()值分配给分区中的行的顺序。

示例如下：

SELECT name,age,score,"ntile"(3) OVER(ORDER BY score desc) as row_num FROM tt