sql row_number 用法

row_number 语句可将select的语句按某一列排序并赋予其连续的编号，示例如下：
语法：ROW_NUMBER () OVER ([ <partition_by_clause> ] <order_by_clause>) 。
备注：ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。
参数：<partition_by_clause> ：将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。
<order_by_clause>：确定将 ROW_NUMBER 值分配给分区中的行的顺序。
返回类型：bigint 。

#示例:
#创建表school.score
+------+------+---------+
| s_id | c_id | s_score |
+------+------+---------+
| 01   | 01   |      80 |
| 01   | 02   |      90 |
| 01   | 03   |      99 |
| 02   | 01   |      70 |
| 02   | 02   |      60 |
| 02   | 03   |      80 |
| 03   | 01   |      80 |
| 03   | 02   |      80 |
| 03   | 03   |      80 |
| 04   | 01   |      50 |
| 04   | 02   |      30 |
| 04   | 03   |      20 |
| 05   | 002  |      73 |
| 05   | 01   |      76 |
| 05   | 02   |      87 |
| 06   | 01   |      31 |
| 06   | 03   |      34 |
| 07   | 02   |      89 |
| 07   | 03   |      98 |
| 08   | 002  |      73 |
+------+------+---------+

语句1：
select s_id, c_id,s_score,row_number() over (order by s_score desc) as 'rank' from school.score;
含义：选取学生id,课程id,分数，按分数降序排列并编号
输出：
+------+------+---------+------+
| s_id | c_id | s_score | rank |
+------+------+---------+------+
| 01   | 03   |      99 |    1 |
| 07   | 03   |      98 |    2 |
| 01   | 02   |      90 |    3 |
| 07   | 02   |      89 |    4 |
| 05   | 02   |      87 |    5 |
| 01   | 01   |      80 |    6 |
| 02   | 03   |      80 |    7 |
| 03   | 01   |      80 |    8 |
| 03   | 02   |      80 |    9 |
| 03   | 03   |      80 |   10 |
| 05   | 01   |      76 |   11 |
| 05   | 002  |      73 |   12 |
| 08   | 002  |      73 |   13 |
| 02   | 01   |      70 |   14 |
| 02   | 02   |      60 |   15 |
| 04   | 01   |      50 |   16 |
| 06   | 03   |      34 |   17 |
| 06   | 01   |      31 |   18 |
| 04   | 02   |      30 |   19 |
| 04   | 03   |      20 |   20 |
+------+------+---------+------+

语句2：partition by 参数
select s_id, c_id,s_score,row_number() over (partition by s_id order by s_score desc) as 'rank' from school.score;
含义：选取学生id,课程id,分数，按学生id分类，并对每个学生的成绩进行内部降序排序并编号
#输出：
+------+------+---------+------+
| s_id | c_id | s_score | rank |
+------+------+---------+------+
| 01   | 03   |      99 |    1 |
| 01   | 02   |      90 |    2 |
| 01   | 01   |      80 |    3 |
| 02   | 03   |      80 |    1 |
| 02   | 01   |      70 |    2 |
| 02   | 02   |      60 |    3 |
| 03   | 01   |      80 |    1 |
| 03   | 02   |      80 |    2 |
| 03   | 03   |      80 |    3 |
| 04   | 01   |      50 |    1 |
| 04   | 02   |      30 |    2 |
| 04   | 03   |      20 |    3 |
| 05   | 02   |      87 |    1 |
| 05   | 01   |      76 |    2 |
| 05   | 002  |      73 |    3 |
| 06   | 03   |      34 |    1 |
| 06   | 01   |      31 |    2 |
| 07   | 03   |      98 |    1 |
| 07   | 02   |      89 |    2 |
| 08   | 002  |      73 |    1 |
+------+------+---------+------+
20 rows in set (0.00 sec)