3182. 查找得分最高的学生

原创已于 2024-12-13 10:12:51 修改 · 669 阅读

28 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #算法 #leetcode #mysql #sql

于 2024-11-27 15:49:26 首次发布

leedcode 数据库题目解析同时被 2 个专栏收录

180 篇文章

订阅专栏

中等plus

102 篇文章

订阅专栏

力扣题目跳转（3182. 查找得分最高的学生 - 力扣（LeetCode））

表：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 升序排序。

case 1 的建表语句。

CREATE TABLE if not exists students (
student_id INT ,
name VARCHAR(255),
major VARCHAR(255)
)
CREATE TABLE if not exists courses (
course_id INT ,
name VARCHAR(255),
credits INT,
major VARCHAR(255)
)

CREATE TABLE if not exists enrollments (
student_id INT,
course_id INT,
semester VARCHAR(255),
grade VARCHAR(10)

);
Truncate table students
insert into students (student_id, name, major) values ('1', 'Alice', 'Computer Science')
insert into students (student_id, name, major) values ('2', 'Bob', 'Computer Science')
insert into students (student_id, name, major) values ('3', 'Charlie', 'Mathematics')
insert into students (student_id, name, major) values ('4', 'David', 'Mathematics')
Truncate table courses
insert into courses (course_id, name, credits, major) values ('101', 'Algorithms', '3', 'Computer Science')
insert into courses (course_id, name, credits, major) values ('102', 'Data Structures', '3', 'Computer Science')
insert into courses (course_id, name, credits, major) values ('103', 'Calculus', '4', 'Mathematics')
insert into courses (course_id, name, credits, major) values ('104', 'Linear Algebra', '4', 'Mathematics')
Truncate table enrollments
insert into enrollments (student_id, course_id, semester, grade) values ('1', '101', 'Fall 2023', 'A')
insert into enrollments (student_id, course_id, semester, grade) values ('1', '102', 'Fall 2023', 'A')
insert into enrollments (student_id, course_id, semester, grade) values ('2', '101', 'Fall 2023', 'B')
insert into enrollments (student_id, course_id, semester, grade) values ('2', '102', 'Fall 2023', 'A')
insert into enrollments (student_id, course_id, semester, grade) values ('3', '103', 'Fall 2023', 'A')
insert into enrollments (student_id, course_id, semester, grade) values ('3', '104', 'Fall 2023', 'A')
insert into enrollments (student_id, course_id, semester, grade) values ('4', '103', 'Fall 2023', 'A')
insert into enrollments (student_id, course_id, semester, grade) values ('4', '104', 'Fall 2023', 'B')

一我们先将这三张表连接起来，这里我只选取部分字段展示，再使用 where 挑选筛选成绩为 A 的部分。

select
    e.student_id,
    e.course_id,
    grade,
    s.name,
    s.major,
    credits
from enrollments e
left join students s on e.student_id = s.student_id
left join courses c on e.course_id = c.course_id
where grade = 'A';

输出如下

二我对表 courses 进行增加了一列 ct2 表示每个 major 中的专业课个数。然后再在条件 where 增加一个为 s.major = c.major 表示筛选学生学自己的专业课数据。最后再使用 cte 去重使得 ct1 = ct2即可。

with tmp as
(select
    e.student_id,
    count(*) over(partition by e.student_id) as ct1,
    ct2
from enrollments e
left join students s on e.student_id = s.student_id
left join (select *, count(*) over(partition by major) as ct2 from courses) c on e.course_id = c.course_id
where grade = 'A' and s.major = c.major)
select distinct student_id from tmp where ct1 = ct2 order by student_id;