查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)

建表语句点击详见

– 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)

SELECT

st.s_id,
st.s_name,
CASE WHEN AVG(sc.s_score) IS  NULL  THEN '无' ELSE ROUND (AVG(sc.s_score) ,2) END AS '平均分'

FROM  student st

LEFT JOIN score sc  
ON sc.s_id=st.s_id
GROUP BY st.s_id 
HAVING AVG(sc.s_score)<60 
OR AVG(sc.s_score) IS NULL