LeetCode MySQL 1280. 学生们参加各科测试的次数

Michael阿明

发布于 2021-02-19 09:59:00

4480

发布于 2021-02-19 09:59:00

文章被收录于专栏：Michael阿明学习之路

文章目录

- 1. 题目
- 2. 解题

1. 题目

学生表: Students

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| student_name  | varchar |
+---------------+---------+

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

科目表: Subjects

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| subject_name | varchar |
+--------------+---------+

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

考试表: Examinations

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| subject_name | varchar |
+--------------+---------+

这张表压根没有主键，可能会有重复行。学生表里的一个学生修读科目表里的每一门科目，而这张考试表的每一行记录就表示学生表里的某个学生参加了一次科目表里某门科目的测试。

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

查询结构格式如下所示：

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         |
+------------+--------------+
Result table:
+------------+--------------+--------------+----------------+
| 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 次。

来源：力扣（LeetCode）链接：https://leetcode-cn.com/problems/students-and-examinations 著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

2. 解题

先进行内连接产生所有的组合

select *
from Students st
join Subjects sub

{"headers": ["student_id", "student_name", "subject_name"], "values": 
[[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"]]}

再把考试表左连接于上表

{"headers": ["student_id", "student_name", "subject_name", "student_id", "subject_name"], "values": 
 [[1, "Alice", "Programming", 1, "Programming"], 
 [1, "Alice", "Physics", 1, "Physics"], 
 [1, "Alice", "Physics", 1, "Physics"], 
 [1, "Alice", "Math", 1, "Math"], 
 [1, "Alice", "Math", 1, "Math"], 
 [1, "Alice", "Math", 1, "Math"], 
 [2, "Bob", "Programming", 2, "Programming"], 
 [2, "Bob", "Physics", null, null], 
 [2, "Bob", "Math", 2, "Math"], 
 [13, "John", "Programming", 13, "Programming"], 
 [13, "John", "Physics", 13, "Physics"], 
 [13, "John", "Math", 13, "Math"], 
 [6, "Alex", "Programming", null, null], 
 [6, "Alex", "Physics", null, null], 
 [6, "Alex", "Math", null, null]]}

再分组

# Write your MySQL query statement below
select t.student_id, t.student_name, t.subject_name,
        sum(if(e.subject_name is null, 0, 1)) attended_exams 
from
(
    select *
    from Students st
    join Subjects sub
) t 
left join Examinations e
on t.student_id = e.student_id and t.subject_name = e.subject_name
group by t.student_id, t.subject_name
order by t.student_id, t.subject_name

720 ms

或者用 count(e.subject_name) attended_exams，null 项不会被计算，返回不为null

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2020/07/19 ，如有侵权请联系 cloudcommunity@tencent.com 删除

腾讯云测试服务