MySQL查询各科前3名

前言：

最近突然被问到了一张学生成绩表，怎么求出各科前三名，当时想了很久也不会，于是现在记录下来。
参考了2篇博客：
1，mysql查询各科成绩前三名_No.03 数据库经典面试之如何取出每科成绩的前三名
2，MySQL 查询各科前三名（考虑成绩并列情况）

准备：

1，创建表

CREATE TABLE `test_course` (

`id` varchar(10) NOT NULL,

`class` varchar(1000) DEFAULT NULL,

`grade` int(100) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

2，插入数据

INSERT INTO test.`test_course` VALUES ('1','yuwen','100');

INSERT INTO test.`test_course` VALUES ('10','yingyu','16');

INSERT INTO test.`test_course` VALUES ('11','yingyu','85');

INSERT INTO test.`test_course` VALUES ('12','yingyu','95');

INSERT INTO test.`test_course` VALUES ('2','yuwen','80');

INSERT INTO test.`test_course` VALUES ('3','shuxue','60');

INSERT INTO test.`test_course` VALUES ('4','shuxue','100');

INSERT INTO test.`test_course` VALUES ('5','shuxue','95');

INSERT INTO test.`test_course` VALUES ('6','yuwen','85');

INSERT INTO test.`test_course` VALUES ('7','shuxue','16');

INSERT INTO test.`test_course` VALUES ('8','yuwen','15');

INSERT INTO test.`test_course` VALUES ('9','yingyu','15');

开始

第一种

SELECT
	S1.*,(
	SELECT
		COUNT( 1 ) 
	FROM
		`test_course` S2 
	WHERE
		S1.CLASS = S2.CLASS 
		AND S2.GRADE > S1.GRADE 
	)+ 1 AS count 
FROM
	`test_course` S1 
HAVING
	count <= 3 
ORDER BY
	S1.CLASS,
	count ;

结果

第二种

SELECT
	t1.*
FROM
	test_course t1
	LEFT JOIN ( SELECT * FROM test_course ) t2 ON t1.class = t2.class 
	AND t1.grade < t2.grade 
GROUP BY
	t1.id,
	class,
	grade 
HAVING
	count( 1 ) < 3 
ORDER BY
	t1.class,
	grade
	DESC

结果