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)