Leetcode 1412. 查找成绩处于中游的学生

1.题目基本信息

1.1.题目描述

表: Student

+---------------------+---------+

| Column Name | Type |

+---------------------+---------+

| student_id | int |

| student_name | varchar |

+---------------------+---------+

student_id 是该表主键(具有唯一值的列)。

student_name 学生名字。

表: Exam

+---------------+---------+

| Column Name | Type |

+---------------+---------+

| exam_id | int |

| student_id | int |

| score | int |

+---------------+---------+

(exam_id, student_id) 是该表主键(具有唯一值的列的组合)。

学生 student_id 在测验 exam_id 中得分为 score。

成绩处于中游的学生是指至少参加了一次测验, 且得分既不是最高分也不是最低分的学生。

编写解决方案，找出在 所有 测验中都处于中游的学生 (student_id, student_name)。不要返回从来没有参加过测验的学生。

返回结果表按照 student_id 排序。

1.2.题目地址

https://leetcode.cn/problems/find-the-quiet-students-in-all-exams/description/

2.解题方法

2.1.解题思路

分组聚合

2.2.解题步骤

第一步，查找每场考试的最高和最低成绩

第二步，查询获取过最高分数或者最低分数的学生的id

第三步，查询参与过测试的学生id

第四步，查询在不在t2表（分数为中游）并且在t3表中（参加过测试）的学生id和学生名字

3.解题代码

sql代码

# Write your MySQL query statement below
# 第一步，查找每场考试的最高和最低成绩
WITH t1 AS (
    SELECT 
        exam_id, 
        MIN(score) AS min_score, 
        MAX(score) AS max_score
    FROM Exam
    GROUP BY exam_id
), 
# 第二步，查询获取过最高分数或者最低分数的学生的id
t2 AS (
    SELECT 
        DISTINCT Exam.student_id
    FROM 
        Exam 
        LEFT JOIN Student ON Exam.student_id = Student.student_id
        LEFT JOIN t1 ON Exam.exam_id = t1.exam_id
    WHERE 
        Exam.score = t1.min_score 
        OR Exam.score = t1.max_score 
), 
# 第三步，查询参与过测试的学生id
t3 AS (
    SELECT DISTINCT student_id FROM Exam
)
# 第四步，查询在不在t2表（分数为中游）并且在t3表中（参加过测试）的学生id和学生名字
SELECT student_id, student_name
FROM Student
WHERE student_id NOT IN (SELECT student_id FROM t2) AND student_id IN (SELECT student_id FROM t3)

4.执行结果