力扣白嫖日记(sql)

前言

练习sql语句，所有题目来自于力扣（https://leetcode.cn/problemset/database/）的免费数据库练习题。

今日题目：

1280.学生们参加各科测试的次数
表：Students

在 SQL 中，主键为 student_id（学生ID）。
该表内的每一行都记录有学校一名学生的信息。

表：Subjects

在 SQL 中，主键为 subject_name（科目名称）。每一行记录学校的一门科目名称。

表：Examinations

这个表可能包含重复数据（换句话说，在 SQL 中，这个表没有主键）。学生表里的一个学生修读科目表里的每一门科目。这张考试表的每一行记录就表示学生表里的某个学生参加了一次科目表里某门科目的测试。

查询出每个学生参加每一门科目测试的次数，结果按 student_id 和 subject_name 排序。

我那不值一提的想法：

首先梳理表内容，一共三个表，第一个学生表，记录了学生id和姓名，第二个科目表，记录了学科名，第三个考试表，记录了学生id和考试科目。其中考试表中的学生id对应学生表中的学生id，考试科目对应课程表中的课程名。其次分析需求，需要查询每个学生参与每一门考试的次数，根据结果表来看，我们需要进行分组求和，但我始终无法让所有科目都显示在结果表中，只能筛选出考试了的学生，后来确实想不出来了，于是看了题解，发现是需要用到cross join 函数，是我的知识盲区了。cross join 是一种连接操作，它能将两个表或多个表中的所有行连接在一起，产生一个包含所有组合的结果集，这样就能保证没有考试的同学业出现结果表中。

那么我么首先将student表和subjects表cross join下看结果

select s.student_id,s.student_name,sub.subject_name
from Students s 
cross join Subjects sub
order by s.student_id,s.student_name,sub.subject_name

这个时候的样子就是我们需要找到结果表的样子。但是还差一点东西，就是记录每次学生考试的次数，也就是还需要连接考试表，然后分组求次数。

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

这个时候，结果还是不对，查找的结果直接是每个学生所有科目的考试总次数，就像这样：

我们还需将考试表中的科目与课程表中的科目相匹配，这样才能查找到对应科目的考试次数。

select s.student_id,s.student_name,sub.subject_name,ifnull(count(e.subject_name),0) 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,s.student_name,sub.subject_name
order by s.student_id,s.student_name,sub.subject_name

在连接条件的后面再加上e.subject_name = sub.subject_name，这样就能保证是每个学生对应科目的考试次数。

结果：

总结：

能运行就行。