一次MySQL join执行错误分析
文章目录
分析
练习一道MySQL题目按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
分析:
- 要获得学生的学号,姓名,因此需要student表
- 要获得所有课程的成绩,需要score 和course表
- 使用join获取所有课程的成绩,使用group by获取平均成绩
- 使用order by desc排序
sql:
SELECT
st.s_id "学号",
st.s_name "姓名",
avg( sc.s_score ) "平均分",
sc1.s_score "语文",
sc2.s_score "数学",
sc3.s_score"英语"
FROM
student st
LEFT JOIN score sc1 ON st.s_id = sc1.s_id
AND sc1.c_id = '01'
LEFT JOIN score sc2 ON sc1.s_id = sc2.s_id
AND sc2.c_id = '02'
LEFT JOIN score sc3 ON sc2.s_id = sc3.s_id
AND sc3.c_id = '03'
LEFT JOIN score sc ON st.s_id = sc.s_id
GROUP BY
st.s_id
ORDER BY
avg( sc.s_score )<