建表语句点击详见
– 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT st.s_id,st.s_name, (CASE WHEN AVG(sc4.s_score) IS NULL THEN 0 ELSE AVG(sc4.s_score) END) "平均分", (CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END) "语文", (CASE WHEN sc2.s_score IS NULL THEN 0 ELSE sc2.s_score END) "数学", (CASE WHEN sc3.s_score IS NULL THEN 0 ELSE sc3.s_score END) "英语" FROM student st LEFT JOIN score sc ON sc.s_id=st.s_id AND sc.c_id="01" LEFT JOIN score sc2 ON sc2.s_id=st.s_id AND sc2.c_id="02" LEFT JOIN score sc3 ON sc3.s_id=st.s_id AND sc3.c_id="03" LEFT JOIN score sc4 ON sc4.s_id=st.s_id GROUP BY st.s_id ORDER BY AVG(sc4.s_score) DESC SELECT st.*, GROUP_CONCAT(c.c_name) 课程, GROUP_CONCAT(sc.s_score) 分数, AVG(sc.s_score) 平均分 FROM student st LEFT JOIN score sc on st.s_id=sc.s_id JOIN course c ON sc.c_id=c.c_id GROUP BY sc.s_id ORDER BY AVG(sc.s_score) DESC;