– 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
SELECT
st.s_id,st.s_name,AVG(sc.s_score)
FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
WHERE sc.s_id IN
(
SELECT
sc.s_id
FROM score sc
WHERE sc.s_score<60 OR sc.s_score IS NULL
GROUP BY sc.s_id HAVING COUNT(1)>=2
)
GROUP BY st.s_id