mysql取出某个维度下，排名前n的记录

问题描述

比如：有学生成绩表tb_grade：

CREATE TABLE `tb_grade` (  
  `studentid` int(10) unsigned DEFAULT NULL,
  `courseid` tinyint(4) DEFAULT NULL,
  `grade` int(11) DEFAULT NULL
) ENGINE=InnoDB;  

mysql> select * from tb_grade;  
+-----------+----------+-------+
| studentid | courseid | grade |
+-----------+----------+-------+
|         1 |        1 |    90 |
|         1 |        2 |    80 |
|         1 |        3 |    85 |
|         2 |        3 |    90 |
|         2 |        2 |    90 |
|         2 |        1 |    70 |
|         3 |        1 |    95 |
|         3 |        2 |    88 |
|         3 |        3 |    92 |
|         5 |        1 |    95 |
|         5 |        2 |    90 |
|         5 |        3 |    92 |
+-----------+----------+-------+
12 rows in set (0.00 sec)  

现在想要查出每个courseid下，grade最高的前2个studentid。

解题思路

• 首先，查出每个courseid下，最高的2个grade

mysql> select courseid, grade from (select t1.courseid courseid, t1.grade grade, if(t1.grade < t2.grade, 1, 0) flag from (select distinct courseid, grade from tb_grade) t1 left join (select distinct courseid, grade from tb_grade) t2 on t1.courseid = t2.courseid) t3 group by courseid, grade having sum(flag) < 2;  
+----------+-------+
| courseid | grade |
+----------+-------+
|        1 |    90 |
|        1 |    95 |
|        2 |    88 |
|        2 |    90 |
|        3 |    90 |
|        3 |    92 |
+----------+-------+
6 rows in set (0.01 sec)  

• 连表查询：

mysql> select t4.courseid, t4.grade, studentid from (select courseid, grade from (select t1.courseid courseid, t1.grade grade, if(t1.grade < t2.grade, 1, 0) flag from (select distinct courseid, grade from tb_grade) t1 left join (select distinct courseid, grade from tb_grade) t2 on t1.courseid = t2.courseid) t3 group by courseid, grade having sum(flag) < 2) t4 inner join tb_grade t5 on t4.courseid = t5.courseid and t4.grade = t5.grade order by courseid, grade asc;  
+----------+-------+-----------+
| courseid | grade | studentid |
+----------+-------+-----------+
|        1 |    90 |         1 |
|        1 |    95 |         3 |
|        1 |    95 |         5 |
|        2 |    88 |         3 |
|        2 |    90 |         2 |
|        2 |    90 |         5 |
|        3 |    90 |         2 |
|        3 |    92 |         3 |
|        3 |    92 |         5 |
+----------+-------+-----------+
9 rows in set (0.00 sec)