mysql中row_number() over功能实现

有时候我们想要获取每组的前n个记录，由于mysql中没有row_number() over 函数，之前部门大佬写了这个方法，觉得很实用，这里展示给大家。
student表：
s_id为学生id，s_name为学生姓名，s_birth为出生年月，s_sex为性别
score表：
s_id 为学生id，c_id为课程id，s_score为对应的成绩。

mysql> select * from student;
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 01   | 赵雷   | 1990-01-01 | 男    |
| 02   | 钱电   | 1990-12-21 | 男    |
| 03   | 孙风   | 1990-05-20 | 男    |
| 04   | 李云   | 1990-08-06 | 男    |
| 05   | 周梅   | 1991-12-01 | 女    |
| 06   | 吴兰   | 1992-03-01 | 女    |
| 07   | 郑竹   | 1989-07-01 | 女    |
| 08   | 王菊   | NULL       | 女    |
+------+--------+------------+-------+
8 rows in set (0.00 sec)
mysql> select * from 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   | 01   |      76 |
| 05   | 02   |      87 |
| 06   | 01   |      31 |
| 06   | 03   |      34 |
| 07   | 02   |      89 |
| 07   | 03   |      98 |
+------+------+---------+
18 rows in set (0.00 sec)

现想要取出每个课程前3名的学生信息、课程id，成绩与对应课程内排名，如下所示：
+------+--------+------------+-------+------+---------+------+
| s_id | s_name | s_birth | s_sex | c_id | s_score | rank |
+------+--------+------------+-------+------+---------+------+
| 01 | 赵雷 | 1990-01-01 | 男 | 01 | 80 | 1 |
| 03 | 孙风 | 1990-05-20 | 男 | 01 | 80 | 2 |
| 05 | 周梅 | 1991-12-01 | 女 | 01 | 76 | 3 |
| 01 | 赵雷 | 1990-01-01 | 男 | 02 | 90 | 1 |
| 07 | 郑竹 | 1989-07-01 | 女 | 02 | 89 | 2 |
| 05 | 周梅 | 1991-12-01 | 女 | 02 | 87 | 3 |
| 01 | 赵雷 | 1990-01-01 | 男 | 03 | 99 | 1 |
| 07 | 郑竹 | 1989-07-01 | 女 | 03 | 98 | 2 |
| 02 | 钱电 | 1990-12-21 | 男 | 03 | 80 | 3 |
+------+--------+------------+-------+------+---------+------+

查询实现如下：

mysql> set @rank:=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select a.*,b.c_id,b.s_score,b.rank from(
    -> select *,@rank:=case when @current_id<>c_id then 1 else @rank+1 end as rank,@current_id:=c_id from score order by c_id,s_score desc)b
    -> left join student a on a.s_id=b.s_id
    -> having rank<=3;

首先看表b的部分,令@current_id=c_id，当c_id不是当前的课程时，rank重新从1开始计数，某则在在当前rank上加1。
需要注意的是，此时的排序需要现基于课程id，再基于成绩逆序。

mysql> select *,@rank:=case when @current_id<>c_id then 1 else @rank+1 end as rank,
    -> @current_id:=c_id from score order by c_id,s_score desc;
+------+------+---------+------+-------------------+
| s_id | c_id | s_score | rank | @current_id:=c_id |
+------+------+---------+------+-------------------+
| 01   | 01   |      80 |    1 | 01                |
| 03   | 01   |      80 |    2 | 01                |
| 05   | 01   |      76 |    3 | 01                |
| 02   | 01   |      70 |    4 | 01                |
| 04   | 01   |      50 |    5 | 01                |
| 06   | 01   |      31 |    6 | 01                |
| 01   | 02   |      90 |    1 | 02                |
| 07   | 02   |      89 |    2 | 02                |
| 05   | 02   |      87 |    3 | 02                |
| 03   | 02   |      80 |    4 | 02                |
| 02   | 02   |      60 |    5 | 02                |
| 04   | 02   |      30 |    6 | 02                |
| 01   | 03   |      99 |    1 | 03                |
| 07   | 03   |      98 |    2 | 03                |
| 02   | 03   |      80 |    3 | 03                |
| 03   | 03   |      80 |    4 | 03                |
| 06   | 03   |      34 |    5 | 03                |
| 04   | 03   |      20 |    6 | 03                |
+------+------+---------+------+-------------------+
18 rows in set (0.00 sec)

在此基础上基于s_id连接student表即可，另外在最后的条件设定中需要用having不能用where，因为在原表中是不存在rank字段的，这是我们为了取数所构造的。