3182. 查找得分最高的学生 🔒

题目描述

表：students

+-------------+----------+
| Column Name | Type     | 
+-------------+----------+
| student_id  | int      |
| name        | varchar  |
| major       | varchar  |
+-------------+----------+
student_id 是这张表的主键（有不同值的列的组合）。
这张表的每一行包含学生 ID，学生姓名和他们的专业。

表格：courses

+-------------+----------+
| Column Name | Type     | 
+-------------+----------+
| course_id   | int      |
| name        | varchar  |
| credits     | int      |
| major       | varchar  |
+-------------+----------+
course_id 是这张表的主键（有不同值的列的组合）。
这张表的每一行包含课程 ID，课程名，课程学分和所属专业。

表：enrollments

+-------------+----------+
| Column Name | Type     | 
+-------------+----------+
| student_id  | int      |
| course_id   | int      |
| semester    | varchar  |
| grade       | varchar  |
+-------------+----------+
(student_id, course_id, semester) 是这张表的主键（有不同值的列的组合）。
这张表的每一行包含学生 ID，课程 ID，学期和获得的学分。

编写一个解决方案来找到参加过他们的 major 提供的 所有课程 并在 所有这些课程中取得等级 A 的人。

返回结果表以 student_id 升序 排序。

结果格式如下所示。

 

示例：

输入：

students 表：

+------------+------------------+------------------+
| student_id | name             | major            |
+------------+------------------+------------------+
| 1          | Alice            | Computer Science |
| 2          | Bob              | Computer Science |
| 3          | Charlie          | Mathematics      |
| 4          | David            | Mathematics      |
+------------+------------------+------------------+

courses 表：

+-----------+-----------------+---------+------------------+
| course_id | name            | credits | major            |
+-----------+-----------------+---------+------------------+
| 101       | Algorithms      | 3       | Computer Science |
| 102       | Data Structures | 3       | Computer Science |
| 103       | Calculus        | 4       | Mathematics      |
| 104       | Linear Algebra  | 4       | Mathematics      |
+-----------+-----------------+---------+------------------+

enrollments 表：

+------------+-----------+----------+-------+
| student_id | course_id | semester | grade |
+------------+-----------+----------+-------+
| 1          | 101       | Fall 2023| A     |
| 1          | 102       | Fall 2023| A     |
| 2          | 101       | Fall 2023| B     |
| 2          | 102       | Fall 2023| A     |
| 3          | 103       | Fall 2023| A     |
| 3          | 104       | Fall 2023| A     |
| 4          | 103       | Fall 2023| A     |
| 4          | 104       | Fall 2023| B     |
+------------+-----------+----------+-------+

输出：

+------------+
| student_id |
+------------+
| 1          |
| 3          |
+------------+

解释：

• Alice (student_id 1) 是计算机科学专业并且修了 “Algorithms” 和 “Data Structures” 课程，都获得了 ‘A’。
• Bob (student_id 2) 是计算机科学专业但没有在全部必修课程中获得 ‘A’。
• Charlie (student_id 3) 是数学专业并且修了 “Calculus” 和 “Linear Algebra” 课程，都获得了 ‘A’。
• David (student_id 4) 是数学专业但没有在全部必修课程中获得 'A'。

注意：输出表以 student_id 升序排序。

解法

方法一：连接表 + 分组

我们可以将 students 表和 courses 按照 major 字段连接起来，然后再将 enrollments 表左连接到上述结果表中，最后按照 student_id 分组，筛选出满足条件的学生。

MySQL

# Write your MySQL query statement below
SELECT student_id
FROM
    students
    JOIN courses USING (major)
    LEFT JOIN enrollments USING (student_id, course_id)
GROUP BY 1
HAVING SUM(grade = 'A') = COUNT(major)
ORDER BY 1;

评论