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)