MySQL刷题：学生们参加各科测试的次数

1. 问题描述

给出三张表：

1. 学生表: Students
   ±--------------±--------+
   | Column Name | Type |
   ±--------------±--------+
   | student_id | int |
   | student_name | varchar |
   ±--------------±--------+
   在 SQL 中，主键为 student_id（学生ID）。该表内的每一行都记录有学校一名学生的信息。
2. 科目表: Subjects
   ±-------------±--------+
   | Column Name | Type |
   ±-------------±--------+
   | subject_name | varchar |
   ±-------------±--------+
   在 SQL 中，主键为 subject_name（科目名称）。
   每一行记录学校的一门科目名称。
4. ±-------------±--------+
   | Column Name | Type |
   ±-------------±--------+
   | student_id | int |
   | subject_name | varchar |
   ±-------------±--------+
   这个表可能包含重复数据（换句话说，在 SQL 中，这个表没有主键）。学生表里的一个学生修读科目表里的每一门科目。这张考试表的每一行记录就表示学生表里的某个学生参加了一次科目表里某门科目的测试。
   实例：
   输入：
5. Students table:
   ±-----------±-------------+
   | student_id | student_name |
   ±-----------±-------------+
   | 1 | Alice |
   | 2 | Bob |
   | 13 | John |
   | 6 | Alex |
   ±-----------±-------------+
6. Subjects table:
   ±-------------+
   | subject_name |
   ±-------------+
   | Math |
   | Physics |
   | Programming |
   ±-------------+
7. Examinations table:
   ±-----------±-------------+
   | student_id | subject_name |
   ±-----------±-------------+
   | 1 | Math |
   | 1 | Physics |
   | 1 | Programming |
   | 2 | Programming |
   | 1 | Physics |
   | 1 | Math |
   | 13 | Math |
   | 13 | Programming |
   | 13 | Physics |
   | 2 | Math |
   | 1 | Math |
   ±-----------±-------------+
   输出：
   ±-----------±-------------±-------------±---------------+
   | student_id | student_name | subject_name | attended_exams |
   ±-----------±-------------±-------------±---------------+
   | 1 | Alice | Math | 3 |
   | 1 | Alice | Physics | 2 |
   | 1 | Alice | Programming | 1 |
   | 2 | Bob | Math | 1 |
   | 2 | Bob | Physics | 0 |
   | 2 | Bob | Programming | 1 |
   | 6 | Alex | Math | 0 |
   | 6 | Alex | Physics | 0 |
   | 6 | Alex | Programming | 0 |
   | 13 | John | Math | 1 |
   | 13 | John | Physics | 1 |
   | 13 | John | Programming | 1 |
   ±-----------±-------------±-------------±---------------+
   解释：
   结果表需包含所有学生和所有科目（即便测试次数为0）：
   Alice 参加了 3 次数学测试, 2 次物理测试，以及 1 次编程测试；
   Bob 参加了 1 次数学测试, 1 次编程测试，没有参加物理测试；
   Alex 啥测试都没参加；
   John 参加了数学、物理、编程测试各 1 次。

2. 问题分析

一拿到这个问题，第一反应肯定是三张表的连结，并且通过 student_id 和 subject_name 进行连结，所以

select st.student_id,st.student_name,s.subject_name,count(ex.subject_name) as attended_exams
from examinations as ex left outer join students as st on ex.student_id =st.student_id 
left outer join subjects as s on ex.subject_name=s.subject_name 
group by st.student_id,st.student_name,s.subject_name
order by st.student_id,s.subject_name;

但是这样的显示结果确实这样的：

但其实最终的目标输出是这样的：

不难看出，这两张表之间的差异在于存在有的学生并没有参加某些科目的考试，比如 Bob 没有参加物理考试，任何表中都没有这一行，那无论怎么连结，也不可能出现 Bob 参加了零场物理考试，解决的办法要用到 交叉连结 我们需要把所有的学生对应所有的科目，这样的思路就是先对学生表和科目表交叉连结：需要注意的是cross join 并没有连结条件，然后和 考试表外连结：

select st.student_id,st.student_name,su.subject_name
from students as st cross join subjects as su
left outer join examinations as ex 
on su.subject_name=ex.subject_name and ex.student_id =st.student_id
order by st.student_id,su.subject_name;

下面再实现分组计数的功能：

select st.student_id,st.student_name,su.subject_name,count(ex.student_id) 
from students as st cross join subjects as su
left outer join examinations as ex 
on su.subject_name=ex.subject_name and ex.student_id =st.student_id
group by st.student_id ,st.student_name ,su.subject_name
order by st.student_id,su.subject_name;

达到了要求，注意，再最后进行 count()计数的时候，一定是对 考试表进行计数的，而不是科目表，因为考试表不是主表，有的单元格是 null

select st.student_id,st.student_name,su.subject_name,ex.student_id ,ex.subject_name 
from students as st cross join subjects as su
left outer join examinations as ex 
on su.subject_name=ex.subject_name and ex.student_id =st.student_id
-- group by st.student_id ,st.student_name ,su.subject_name
order by st.student_id,su.subject_name;

我们看一下未分组之前的

注意分组的形式，就知道肯定是对 考试表的 subject_name 或者 id 进行计数。
最后看一下完整版的：

select st.student_id,st.student_name,su.subject_name,count(ex.student_id)as attended_exams 
from students as st cross join subjects as su
left outer join examinations as ex 
on su.subject_name=ex.subject_name and ex.student_id =st.student_id
group by st.student_id ,st.student_name ,su.subject_name
order by st.student_id,su.subject_name;