【SQL】1280. 学生们参加各科测试的次数 (笛卡尔积)

前述

知识点回顾：数据库中的四大join & 笛卡尔乘积（以MySQL为例）

• 笛卡尔积的两种写法
  • select * from stu,class;
  • select * from stu cross join class;

题目描述

leetcode题目：1280. 学生们参加各科测试的次数

Code

写法一

先把Students表和Subjects表进行笛卡尔积，得到表S
再左外连接统计好的E表

select 
    S.student_id, 
    S.student_name,
    S.subject_name,
    ifnull(cnt, 0) as attended_exams
from (
    select *
    from Students, Subjects
) S 
left join (
    select *, count(*) as cnt
    from Examinations
    group by student_id, subject_name
) E
on S.student_id = E.student_id and S.subject_name = E.subject_name
order by S.student_id, S.subject_name

写法二

select 
    Stu.student_id, 
    Stu.student_name,
    Sub.subject_name,
    ifnull(cnt, 0) as attended_exams
from Students Stu
cross join Subjects Sub
left join (
    select *, count(*) as cnt
    from Examinations
    group by student_id, subject_name
) E
on Stu.student_id = E.student_id and Sub.subject_name = E.subject_name
order by Stu.student_id, Sub.subject_name