力扣数据库热题70--No.1280题解

官方难度简单，体感稍繁琐，涉及多个表的处理。

第一是聚合，通过group by分组求已有记录的次数。

第二是cross join，通过笛卡尔积获得全量所有组合。

第三是联表，通过left join以全量组合为底表，获取对应分组计数；其中用ifnull做处理，on搭配多个条件。

with tmp1 as
(select *,count(*) as cnt
from Examinations
group by student_id,subject_name)

,tmp2 as
(select *
from Students cross join Subjects)

select tmp2.student_id as student_id,student_name,
    tmp2.subject_name as subject_name,ifnull(cnt,0) as attended_exams
from tmp2 left join tmp1
on tmp2.student_id = tmp1.student_id
and tmp2.subject_name = tmp1.subject_name
order by student_id,subject_name