Leetcode- 学生们参加各科测试的次数 / 超过5名学生的课

6.学生们参加各科测试的次数

链接:https://leetcode-cn.com/problems/students-and-examinations/

select s.student_id,
s.student_name,
sub.subject_name,
count(e.subject_name) as attended_exams
from Students s cross join Subjects sub
    left join Examinations e on s.student_id=e.student_id
and   e.subject_name=sub.subject_name
group by s.student_id,sub.subject_name
order by s.student_id,sub.subject_name;

解析:

cross join 返回两张表的笛卡尔积​

首先从结果来看结果的前三列列为表students和subjects的交叉连接，也就是笛卡尔积

而最后一列为每个学生参加每个学科的测试次数，也就是分组统计

7.超过5名学生的课

链接:https://leetcode-cn.com/problems/classes-more-than-5-students/

select class from(
    select class,
    count(distinct student) as time
    from courses 
    group by class
)as T
where time>=5;

注意:学生在每个课中不应该被重复计算