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)  

原文出处：timd -> http://timd.cn/2017/10/09/mysql-topn/