建表语句点击详见
– 查询各科成绩前三名的记录
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