题目
解题思路
先看题目,再看结果
从结果可以看出
结果的前三列列为表students和subjects的交叉连接,也就是笛卡尔积
而最后一列为每个学生参加每个学科的测试次数,也就是分组统计
解题步骤
1.求笛卡尔积
select * from `Students` as s1 cross join `Subjects` as s2
2.分组统计
select student_id, subject_name, count(student_id) as attended_exams from `Examinations` group by student_id,subject_name
3.将两部连接起来,以第一部分为准,也就是左连接
然后没考试的,也就是测试次数为0,使用ifnull判断设0
select ss.student_id as student_id, ss.student_name as student_name, ss.subject_name as subject_name, ifnull(e1.attended_exams, 0) as attended_exams from ( select * from `Students` as s1 cross join `Subjects` as s2 ) as ss left join ( select student_id, subject_name, count(student_id) as attended_exams from `Examinations` group by student_id,subject_name ) as e1 on ss.student_id = e1.student_id and ss.subject_name = e1.subject_name order by ss.student_id, ss.subject_name