Mysql强化练习-难题之查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

SELECT t1.* FROM
(SELECT st.*,c.c_id,c.c_name,sc.s_score FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
INNER JOIN course c ON c.c_id =sc.c_id AND c.c_id="01"
ORDER BY sc.s_score DESC LIMIT 1,2) t1

UNION ALL

SELECT t2.* FROM
(SELECT st.*,c.c_id,c.c_name,sc.s_score FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
INNER JOIN course c ON c.c_id =sc.c_id AND c.c_id="02"
ORDER BY sc.s_score DESC LIMIT 1,2) t2

UNION ALL

SELECT t3.* FROM
(SELECT st.*,c.c_id,c.c_name,sc.s_score FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
INNER JOIN course c ON c.c_id =sc.c_id AND c.c_id="03"
ORDER BY sc.s_score DESC LIMIT 1,2) t3

SELECT t1.* FROM (student) 

SELECT st.*,c.c_id,c.c_name,sc.s_score FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id

INNER JOIN course c ON c.c_id =sc.c_id AND c.c_id="01"
ORDER BY sc.s_score DESC LIMIT 1,2