列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)经典面试题

创建表

CREATE TABLE t_student_score(
stuid INT(11),
NAME VARCHAR(50),
SUBJECT VARCHAR(50),
score INT(11)
)

 
INSERT INTO t_student_score VALUES(10001,'张三','语文',89);
INSERT INTO t_student_score VALUES(10001,'张三','数学',95);
INSERT INTO t_student_score VALUES(10001,'张三','外语',70);
INSERT INTO t_student_score VALUES(10002,'李四','语文',95);
INSERT INTO t_student_score VALUES(10002,'李四','数学',80);
INSERT INTO t_student_score VALUES(10002,'李四','外语',75);
INSERT INTO t_student_score VALUES(10003,'王五','语文',85);
INSERT INTO t_student_score VALUES(10003,'王五','数学',90);
INSERT INTO t_student_score VALUES(10003,'王五','外语',70);

 

--  列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)

SELECT t1.name,t1.SUBJECT,t1.score FROM t_student_score t1 WHERE
(
SELECT COUNT(1) FROM t_student_score t2 WHERE
t1.SUBJECT=t2.SUBJECT AND t2.score>=t1.score
)<=2
ORDER BY t1.SUBJECT,t1.score DESC;

SELECT  a.*
FROM  t_student_score a
WHERE 2 > (
SELECT COUNT(t.stuid) FROM t_student_score t WHERE t.SUBJECT = a.SUBJECT AND t.score > a.score
)
ORDER BY
a.SUBJECT,a.score ;