– 按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績
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;