-- 25、查询各科成绩前三名的记录

SELECT a.* FROM (
SELECT st.s_id,st.s_name,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 0,3) a

UNION ALL 

SELECT b.* FROM (
SELECT st.s_id,st.s_name,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 0,3) b

UNION ALL

SELECT c.* FROM (
SELECT st.s_id,st.s_name,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 0,3) c