1.在分析问题前，我们先把数据准备好。
CREATE TABLE student(
name VARCHAR(10),
subject VARCHAR(10),
score INT(10)
);
2.插入数据
INSERT INTO student VALUES
("张三","java",71),
("李四","java",82),
("王五","java",90),
("赵六","java",98),
("孙七","java",90),
("张三","python",81),
("李四","python",92),
("王五","python",93),
("赵六","python",97),
("孙七","python",70);
思路
查询各科成绩前三名，前三名都有一个特点，比他们成绩高的人数小于3，成绩就是前三名。
SELECT T1.*
FROM student T1
LEFT JOIN (SELECT DISTINCT subject, score FROM student) 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
