文章目录
题目
代码(7.15 首刷看解析)
笛卡尔积 cross join
- Student表和Subjects表进行笛卡尔积连接(Student JOIN Subjects)
- 在第一点的基础上拼接Examinations中的每个学生参加每门科目的数量。
- 根据案例可以看出,学生名单必须完整,在Examinations表中不存在则为0。所以使用左连接LEFT JOIN进行连接(Student JOIN Subjects LEFT JOIN Examinations)
- 注意排序不是按Examinations表进行排序的,因为存在NULL(所以要用su.subject_name),下图第四个字段就是Examinations的student_id
# Write your MySQL query statement below
select s.student_id, s.student_name, su.subject_name, count(e.student_id) attended_exams
from Students s cross join subjects su left join examinations e on s.student_id = e.student_id
and su.subject_name = e.subject_name
group by s.student_id, su.subject_name order by s.student_id, su.subject_name;