学生们参加各科测试的次数(必看经典题)

SQL专题

本文最后更新于2023年09月04日，已超过381天没有更新。如果文章内容或图片资源失效，请留言反馈，我会及时处理，谢谢！

如题：
查询出每个学生参加每一门科目测试的次数，结果按 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 次。

解答：

# Write your MySQL query statement below
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;

思路：
我们通过一个子查询创建了表 grouped，它统计每个学生参加每个科目的考试次数。

SELECT 
    student_id, subject_name, COUNT(*) AS attended_exams
FROM 
    Examinations
GROUP BY 
    student_id, subject_name

Alex并没有参加考试记录，如何将它也放入表中？
为了获得 (student_id，subject_name) 的所有组合，我们使用交叉联接将表 Student 中的每一行与表 Subject 中的每一行组合在一起，从而得到两个表中的 student_id 和 subject_name 的所有可能组合。

SELECT 
    *
FROM
    Students s
CROSS JOIN
    Subjects sub

最后使用左连接链接即可

在左连接之后， grouped.attended_exams 列可能有 null 值，我们使用 IFNULL() 函数将其替换为0

朗读

Toasobi的博客

学生们参加各科测试的次数(必看经典题)

互动读者

热门文章