-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

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