有時(shí)候我們想要獲取每組的前n個(gè)記錄钟些，由于mysql中沒有row_number() over 函數(shù)，之前部門大佬寫了這個(gè)方法，覺得很實(shí)用，這里展示給大家尊残。
student表：
s_id為學(xué)生id，s_name為學(xué)生姓名淤堵，s_birth為出生年月，s_sex為性別
score表：
s_id 為學(xué)生id顷扩，c_id為課程id拐邪，s_score為對(duì)應(yīng)的成績(jī)。
mysql> select * from student;
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 01 | 趙雷 | 1990-01-01 | 男 |
| 02 | 錢電 | 1990-12-21 | 男 |
| 03 | 孫風(fēng) | 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)
現(xiàn)想要取出每個(gè)課程前3名的學(xué)生信息隘截、課程id扎阶，成績(jī)與對(duì)應(yīng)課程內(nèi)排名，如下所示：
+------+--------+------------+-------+------+---------+------+
| s_id | s_name | s_birth | s_sex | c_id | s_score | rank |
+------+--------+------------+-------+------+---------+------+
| 01 | 趙雷 | 1990-01-01 | 男 | 01 | 80 | 1 |
| 03 | 孫風(fēng) | 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 |
+------+--------+------------+-------+------+---------+------+
查詢實(shí)現(xiàn)如下：
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婶芭，當(dāng)c_id不是當(dāng)前的課程時(shí)东臀，rank重新從1開始計(jì)數(shù)，某則在在當(dāng)前rank上加1犀农。
需要注意的是惰赋，此時(shí)的排序需要現(xiàn)基于課程id，再基于成績(jī)逆序。
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)
在此基礎(chǔ)上基于s_id連接student表即可赁濒，另外在最后的條件設(shè)定中需要用having不能用where轨奄，因?yàn)樵谠碇惺遣淮嬖趓ank字段的，這是我們?yōu)榱巳?shù)所構(gòu)造的拒炎。
