mysql 取前200名_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/