3188. 查找得分最高的学生 II 🔒

题目描述

表：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           |       
| mandatory   | enum              |      
+-------------+-------------------+
course_id 是这张表的主键。 
mandatory 是 ('Yes', 'No') 的枚举类型。
每一行包含课程 ID，课程名，学分，所属专业，以及该课程是否必修。

表：enrollments

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

编写一个解决方案来查找满足下述标准的学生：

已经 修完他们专业中所有的必修课程 和 至少两个 选修课程。
在 所有必修课程 中取得等级 A 并且 选修课程 至少取得 B。
保持他们所有课程（包括不属于他们专业的）的平均 GPA 至少在 2.5 以上。

返回结果表以 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            | mandatory|
 +-----------+-------------------+---------+------------------+----------+
 | 101       | Algorithms        | 3       | Computer Science | yes      |
 | 102       | Data Structures   | 3       | Computer Science | yes      |
 | 103       | Calculus          | 4       | Mathematics      | yes      |
 | 104       | Linear Algebra    | 4       | Mathematics      | yes      |
 | 105       | Machine Learning  | 3       | Computer Science | no       |
 | 106       | Probability       | 3       | Mathematics      | no       |
 | 107       | Operating Systems | 3       | Computer Science | no       |
 | 108       | Statistics        | 3       | Mathematics      | no       |
 +-----------+-------------------+---------+------------------+----------+

enrollments 表：

 +------------+-----------+-------------+-------+-----+
 | student_id | course_id | semester    | grade | GPA |
 +------------+-----------+-------------+-------+-----+
 | 1          | 101       | Fall 2023   | A     | 4.0 |
 | 1          | 102       | Spring 2023 | A     | 4.0 |
 | 1          | 105       | Spring 2023 | A     | 4.0 |
 | 1          | 107       | Fall 2023   | B     | 3.5 |
 | 2          | 101       | Fall 2023   | A     | 4.0 |
 | 2          | 102       | Spring 2023 | B     | 3.0 |
 | 3          | 103       | Fall 2023   | A     | 4.0 |
 | 3          | 104       | Spring 2023 | A     | 4.0 |
 | 3          | 106       | Spring 2023 | A     | 4.0 |
 | 3          | 108       | Fall 2023   | B     | 3.5 |
 | 4          | 103       | Fall 2023   | B     | 3.0 |
 | 4          | 104       | Spring 2023 | B     | 3.0 |
 +------------+-----------+-------------+-------+-----+

输出：

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

解释：

Alice (student_id 1) 是计算机科学专业并且修了 Algorithms 和 Data Structures，都取得了 A。她同时选修了 Machine Learning 和 Operating Systems，分别取得了 A 和 B。
Bob (student_id 2) 是计算机科学专业但没有在所有需求的课程中取得 A。
Charlie (student_id 3) 是数学专业并且修了 Calculus 和 Linear Algebra，都取得了 A。他同时选修了 Probability 和 Statistics，分别取得了 A 和 B。
David (student_id 4) 是数学专业但没有在所有需要的课程中取得 A。

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

解法

方法一：连接 + 分组 + 条件过滤

我们首先筛选出平均 GPA 大于等于 2.5 的学生，记录在 T 表中。

然后，我们将 T 表与 students 表按照 student_id 进行连接，然后与 courses 表按照 major 进行连接，再与 enrollments 表按照 student_id 和 course_id 进行左连接。

接下来，我们按照学生 ID 进行分组，然后使用 HAVING 子句过滤出符合条件的学生，最后按照学生 ID 进行排序。

MySQL

# Write your MySQL query statement below
WITH
    T AS (
        SELECT student_id
        FROM enrollments
        GROUP BY 1
        HAVING AVG(GPA) >= 2.5
    )
SELECT student_id
FROM
    T
    JOIN students USING (student_id)
    JOIN courses USING (major)
    LEFT JOIN enrollments USING (student_id, course_id)
GROUP BY 1
HAVING
    SUM(mandatory = 'yes' AND grade = 'A') = SUM(mandatory = 'yes')
    AND SUM(mandatory = 'no' AND grade IS NOT NULL) = SUM(mandatory = 'no' AND grade IN ('A', 'B'))
    AND SUM(mandatory = 'no' AND grade IS NOT NULL) >= 2
ORDER BY 1;

3188. 查找得分最高的学生 II 🔒

题目描述

解法

方法一：连接 + 分组 + 条件过滤

评论