-- 24、查询学生平均成绩及其名次

SELECT st.s_id,st.s_name,ROUND((CASE WHEN AVG(sc.s_score) IS NULL THEN 0 ELSE AVG(sc.s_score) END),2) "avg" FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
GROUP BY st.s_id ORDER BY  AVG(sc.s_score) DESC