MySQL 查询各科前三名

MySQL 查询各科前三名

看了网上很多sql写法，多多少少都有些问题，写复杂了或没考虑到存在相同分数的情况，或者写出的sql根本就是错的。

在分析问题前，我们先把数据准备好。

1.创建表

CREATE TABLE score_t(
name 		VARCHAR(10),
subject		VARCHAR(10),
score		INT(10)
);

2.插入数据

INSERT INTO score_t VALUES
("张三","java",71),
("李四","java",82),
("王五","java",90),
("赵六","java",98),
("孙七","java",90),
("张三","python",81),
("李四","python",92),
("王五","python",93),
("赵六","python",97),
("孙七","python",70);

思路

查询各科成绩前三名，前三名都有一个特点，比他们成绩高的人数小于3，成绩就是前三名。

错误的写法

我们先来看目前网上呼声最高的SQL写法：

SELECT 		T1.*
FROM 		score_t T1
WHERE		(SELECT COUNT(1) FROM score_t WHERE T1.subject = subject AND T1.score < score) < 3
ORDER BY 	subject,score DESC

运行结果

你看，这个写法的思路确实是正确的，但是它忽略了存在相同分数的情况，这在实际情况中不是我们想要的结果，人家成绩确实是前三你为什么不给人家排名呀？你要气死人家？所以我说它是错的。

正确的写法

SELECT		T1.*
FROM 		score_t T1
LEFT JOIN	(SELECT DISTINCT subject, score FROM score_t) T2
ON 			T1.subject = T2.subject
AND 		T1.score < T2.score
GROUP BY 	name,subject,score
HAVING		COUNT(1) < 3
ORDER BY 	subject,score DESC

运行结果

这里只需要先将T2表数据按科目和分数，去重，再查找满足比它分数高的记录数 < 3 的数据即可。