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

建表语句点击详见

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

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