【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 cntfrom Examinationsgroup 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 cntfrom Examinationsgroup 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