-- 42、查询每门功成绩最好的前两名

SELECT a.* FROM (SELECT st.s_id,st.s_name,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 0,2) a UNION ALL SELECT b.* FROM (SELECT st.s_id,st.s_name,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 0,2) b UNION ALL SELECT c.* FROM (SELECT st.s_id,st.s_name,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 0,2) c