rank()函数

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子句按分数对结果集进行排序。