RANK() OVER (
PARTITION BY <expression>[{,<expression>...}]
ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)
按照某字段的排序结果添加排名,但它是跳跃的、间断的排名
(1)若按照分数直接进行排序的话,例如按照score进行排名
两个并列第一名后,下一个是第三名。
SELECT score, rank() over(ORDER BY score desc) as 'Rank'
FROM rank;
+------+---------+
| score| Rank |
+------+---------+
| 100 | 1 |
| 100 | 1 |
| 95 | 3 |
| 95 | 3 |
| 95 | 3 |
| 90 | 6 |
| 89 | 7 |
+------+---------+
7 rows in set (0.02 sec)
2)若按照某个字段分区进行排序的话,例如按照name进行分区,根据分数进行排名:
SELECT name ,
score ,
rank() over(partition by name ORDER BY score desc) as 'Rank'
FROM rank;
+------+------+---------+
| name | score| Rank |
+------+------+---------+
| a | 100 | 1 |
| a | 90 | 2 |
| a | 89 | 3 |
| b | 100 | 1 |
| c | 95 | 1 |
| d | 95 | 1 |
| e | 95 | 1 |
+------+------+---------+
7 rows in set (0.02 sec)
首先,PARTITION BY子句按姓名将结果集分成多个分区。
然后,ORDER BY子句按分数对结果集进行排序。