mysql leetcode 1280. 学生们参加各科测试的次数 解题思路 一步一步来

题目

 

 

 

 解题思路

先看题目，再看结果
从结果可以看出
结果的前三列列为表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