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

建表语句点击详见

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

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