我是一名立志把细节说清楚的博主，欢迎【关注】🎉 ~
原创不易， 如果有帮助 ，记得【点赞】【收藏】 哦~ ❥(^_-)~
如有错误、疑惑，欢迎【评论】指正探讨，我会尽可能第一时间回复的，谢谢支持
原文
学生表: Students
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| student_id | int |
| student_name | varchar |
+---------------+---------+
在 SQL 中，主键为 student_id（学生ID）。
该表内的每一行都记录有学校一名学生的信息。
科目表: Subjects
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| subject_name | varchar |
+--------------+---------+
在 SQL 中，主键为 subject_name（科目名称）。
每一行记录学校的一门科目名称。
考试表: Examinations
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| student_id | int |
| subject_name | varchar |
+--------------+---------+
这个表可能包含重复数据（换句话说，在 SQL 中，这个表没有主键）。
学生表里的一个学生修读科目表里的每一门科目。
这张考试表的每一行记录就表示学生表里的某个学生参加了一次科目表里某门科目的测试。
查询出每个学生参加每一门科目测试的次数，结果按 student_id 和 subject_name 排序。
查询结构格式如下所示。
示例 1：
输入：
Students table
+------------+--------------+
| student_id | student_name |
+------------+--------------+
| 1 | Alice |
| 2 | Bob |
| 13 | John |
| 6 | Alex |
+------------+--------------+
Subjects table:
+--------------+
| subject_name |
+--------------+
| Math |
| Physics |
| Programming |
+--------------+
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 次。
题解
SELECT
s.student_id, s.student_name, sub.subject_name, IFNULL(grouped.attended_exams, 0) AS attended_exams
FROM
Students s
CROSS JOIN
Subjects sub
LEFT JOIN (
SELECT student_id, subject_name, COUNT(*) AS attended_exams
FROM Examinations
GROUP BY student_id, subject_name
) grouped
ON s.student_id = grouped.student_id AND sub.subject_name = grouped.subject_name
ORDER BY s.student_id, sub.subject_name;
解题笔记
本题解题过程，主要有 4 个步骤：
- 查询出有关参加考试次数的数据。
- 获取 学生名称 与 考试科目 的所有组合。
- 通过左连接查询合并数据。
- 处理学生没有参加的考试，将参加次数由
NULL处理为 0。
第一步：我们需要先查询统计处每个学生参加每个科目的考试次数。
SELECT
student_id, subject_name, COUNT(*) AS attended_exams
FROM
Examinations
GROUP BY
student_id, subject_name
GROUP BY关键字，对结果进行分组。- 关键字详细说明请看文章：【MySQL】数据分组（关键字：GROUP BY）过滤分组（关键字：HAVING）
结果如下：
+---------------+--------------+----------------+
| student_id | subject_name | attended_exams |
+---------------+--------------+----------------+
| 1 | Math | 3 |
| 1 | Physics | 2 |
| 1 | Programming | 1 |
| 2 | Programming | 1 |
| 13 | Math | 1 |
| 13 | Programming | 1 |
| 13 | Physics | 1 |
| 2 | Math | 1 |
+---------------+--------------+----------------+
第二步：为了获得 (student_id，subject_name) 的所有组合，我们使用交叉联接将表 Student 中的每一行与表 Subject 中的每一行组合在一起，从而得到两个表中的 student_id 和 subject_name 的所有可能组合。
SELECT
*
FROM
Students s
CROSS JOIN
Subjects sub
CROSS JOIN关键字，交叉连接，又称笛卡尔积。
结果如下：
+------------+--------------+--------------+
| student_id | student_name | subject_name |
+------------+--------------+--------------+
| 1 | Alice | Programming |
| 1 | Alice | Physics |
| 1 | Alice | Math |
| 2 | Bob | Programming |
| 2 | Bob | Physics |
| 2 | Bob | Math |
| 13 | John | Programming |
| 13 | John | Physics |
| 13 | John | Math |
| 6 | Alex | Programming |
| 6 | Alex | Physics |
| 6 | Alex | Math |
+------------+--------------+--------------+
第三步：我们将第一步查询的表作为子查询表，命名为表 grouped，并与第二步查询进行左连接，使用 (student_id，subject_name) 对作为标识符来保留所有组合，合并两个表。
第四步：对 grouped.attended_exams 列可能存在 null 值，我们使用 IFNULL() 函数将其替换为0。
最终SQL：
SELECT
s.student_id, s.student_name, sub.subject_name, IFNULL(grouped.attended_exams, 0) AS attended_exams
FROM
Students s
CROSS JOIN
Subjects sub
LEFT JOIN (
SELECT student_id, subject_name, COUNT(*) AS attended_exams
FROM Examinations
GROUP BY student_id, subject_name
) grouped
ON s.student_id = grouped.student_id AND sub.subject_name = grouped.subject_name
ORDER BY s.student_id, sub.subject_name;
LEFT JOIN关键字，左连接，又称左外连接，以LEFT JOIN关键字作为标准，左边的表称为左表，右边的表称为右表。ON关键字修饰连接条件，左表数据全部展示，右表数据根据连接条件展示对应数据，如右表中无对应数据，则使用NULL填充右表中无数据的字段。IFNULL()函数，格式： IFNULL(a, b)。- 如果
a不为NULL，则IFNULL()的返回值为a；否则其返回值为b。 - 返回值可以为数字，也可以是字符串。
- 如果
ORDER BY关键字，结果排序。- 关键字详解，请看文章：【MySQL】查询数据，对结果进行排序（关键字：ORDER BY）
我是一名立志把细节说清楚的博主，欢迎【关注】🎉 ~
原创不易， 如果有帮助 ，记得【点赞】【收藏】 哦~ ❥(^_-)~
如有错误、疑惑 ，欢迎【评论】指正探讨，我会尽可能第一时间回复的，谢谢支持
扫一扫
584
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
