mysql中的几种排名函数

mysql中的排名函数

mysql里面的排名函数，涉及有以下几个：

rank()、dense_rank()、row_number()

1、rank() 函数

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

该函数按照某字段的排序结果添加排名，但它是跳跃的、间断的排名

（1）若按照数值直接进行排序的话，例如按照分组统计的数量进行排名

     两个并列第一名后，下一个是第三名。

具体实例

select City,
RANK() over(ORDER BY COUNT(Id) DESC) 'rank',
COUNT(Id) num,IndustrySubGroupName from base_companyinfo 
WHERE IndustrySubGroupName='工业机器人'
GROUP BY City

查询结果

+---------------------+-----------------------+
|City    |rank    |num    |IndustrySubGroupName |
+---------------------+-----------------------+
|410100    |     1 |     4 |           工业机器人 |
|410883    |      2 |     1 |           工业机器人 |
|410300    |     2 |     1    |           工业机器人 |
|410185    |     4 |     1 |           工业机器人 |
|410700    |     5 |      1 |           工业机器人 |
|411000    |      6 |      1 |           工业机器人 |
+----------------------+----------------------+
6 rows in set (0.02 sec)

（2）若按照某个字段分区进行排序的话，例如按照IndustrySubGroupName进行分区，再根据数值进行排名：

select City,
RANK() over(partition by IndustrySubGroupName ORDER BY COUNT(Id) DESC)  'rank',
COUNT(Id) num,IndustrySubGroupName from base_companyinfo 
 -- WHERE IndustrySubGroupName='工业机器人'
GROUP BY City
-- ORDER BY num desc

• 首先，PARTITION BY子句按第二产业名称-IndustrySubGroupName将结果集分成多个分区。
• 然后，ORDER BY子句按数值COUNT(Id)对结果集进行排序。

会得到与上面不一样的排名结果：

即：遇到不同IndustrySubGroupName分区，会重新计算该分区的排名

2、row_number() 函数

MySQL ROW_NUMBER()从8.0版开始引入了功能。这ROW_NUMBER()是一个窗口函数或分析函数，它为从1开始应用的每一行分配一个序号

语法结构如下：

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

3、dense_rank() 函数

dense_rank()是的排序数字是连续的、不间断。当有相同的分数时，它们的排名结果是并列的，例如，1,2,2,3。

语法结构：

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

具体实例

select City,
DENSE_RANK() over(ORDER BY COUNT(Id) DESC)  'rank',
COUNT(Id) num,IndustrySubGroupName from base_companyinfo 
 WHERE IndustrySubGroupName='工业机器人'
GROUP BY City
-- ORDER BY num desc

结果如下：

+---------------------+-----------------------+
|City    |rank    |num    |IndustrySubGroupName |
+---------------------+-----------------------+
|410100    |     1 |     4 |           工业机器人 |
|410883    |      2 |     3 |           工业机器人 |
|410300    |     2 |     3    |           工业机器人 |
|410185    |     3 |     2 |           工业机器人 |
|410700    |     4 |      1 |           工业机器人 |
|411000    |      5 |      0 |           工业机器人 |
+----------------------+----------------------+
6 rows in set (0.02 sec)

若按照某个字段分区进行排序的话，例如按照IndustrySubGroupName进行分区，根据数值进行排名，也是会并列显示

select City,
DENSE_RANK() over(partition by IndustrySubGroupName ORDER BY COUNT(Id) DESC)  'rank',
COUNT(Id) num,IndustrySubGroupName from base_companyinfo 
-- WHERE IndustrySubGroupName='工业机器人'
GROUP BY City
-- ORDER BY num desc

结果如下：

当从工业机器人，切换到特种机器人时，就成了另一个分区，排名会重新计算

+---------------------+-----------------------+
|City    |rank    |num    |IndustrySubGroupName |
+---------------------+-----------------------+
|410100    |     1 |     4 |           工业机器人 |
|410883    |      2 |     3 |           工业机器人 |
|410300    |     2 |     3    |           工业机器人 |
|410185    |     3 |     2 |           工业机器人 |
|410700    |     1 |      4 |           特种机器人 |
|411000    |      2 |      3 |           特种机器人 |
+----------------------+----------------------+
6 rows in set (0.02 sec)