# Write your MySQL query statement below
SELECT a.student_id, a.student_name, b.subject_name, COUNT(e.subject_name) AS attended_exams
FROM Students a CROSS JOIN Subjects b
LEFT JOIN Examinations e ON a.student_id = e.student_id AND b.subject_name = e.subject_name
GROUP BY a.student_id, b.subject_name
ORDER BY a.student_id, b.subject_name
#使用笛卡尔积,cross join将学生和表对应起来
#不能用COUNT(*),要用COUNT(e.subject_name),因为e.subject_name中才有NULL
《学习MySQL语句》——第十八天
于 2023-04-27 16:27:16 首次发布