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

建表语句点击详见

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

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;