建表语句点击详见
– 查询平均成绩小于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
