1412. 查找成绩处于中游的学生（难度：困难）

题目描述

（通过次数5,191 | 提交次数9,358，通过率55.47%）

表: 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.

成绩处于中游的学生是指至少参加了一次测验,且得分既不是最高分也不是最低分的学生。
写一个 SQL 语句，找出在 所有 测验中都处于中游的学生 (student_id, student_name)。
不要返回从来没有参加过测验的学生。返回结果表按照student_id排序。

查询结果格式如下。
Student 表：
+-------------+---------------+
| student_id  | student_name  |
+-------------+---------------+
| 1           | Daniel        |
| 2           | Jade          |
| 3           | Stella        |
| 4           | Jonathan      |
| 5           | Will          |
+-------------+---------------+

Exam 表：
+------------+--------------+-----------+
| exam_id    | student_id   | score     |
+------------+--------------+-----------+
| 10         |     1        |    70     |
| 10         |     2        |    80     |
| 10         |     3        |    90     |
| 20         |     1        |    80     |
| 30         |     1        |    70     |
| 30         |     3        |    80     |
| 30         |     4        |    90     |
| 40         |     1        |    60     |
| 40         |     2        |    70     |
| 40         |     4        |    80     |
+------------+--------------+-----------+

Result 表：
+-------------+---------------+
| student_id  | student_name  |
+-------------+---------------+
| 2           | Jade          |
+-------------+---------------+

对于测验 1: 学生 1 和 3 分别获得了最低分和最高分。
对于测验 2: 学生 1 既获得了最高分, 也获得了最低分。
对于测验 3 和 4: 学生 1 和 4 分别获得了最低分和最高分。
学生 2 和 5 没有在任一场测验中获得了最高分或者最低分。
因为学生 5 从来没有参加过任何测验, 所以他被排除于结果表。
由此, 我们仅仅返回学生 2 的信息。

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/find-the-quiet-students-in-all-exams

//测试数据
Create table If Not Exists Student (student_id int, student_name varchar(30));
Create table If Not Exists Exam (exam_id int, student_id int, score int);

insert into Student (student_id, student_name) values ('1', 'Daniel');
insert into Student (student_id, student_name) values ('2', 'Jade');
insert into Student (student_id, student_name) values ('3', 'Stella');
insert into Student (student_id, student_name) values ('4', 'Jonathan');
insert into Student (student_id, student_name) values ('5', 'Will');

insert into Exam (exam_id, student_id, score) values ('10', '1', '70');
insert into Exam (exam_id, student_id, score) values ('10', '2', '80');
insert into Exam (exam_id, student_id, score) values ('10', '3', '90');
insert into Exam (exam_id, student_id, score) values ('20', '1', '80');
insert into Exam (exam_id, student_id, score) values ('30', '1', '70');
insert into Exam (exam_id, student_id, score) values ('30', '3', '80');
insert into Exam (exam_id, student_id, score) values ('30', '4', '90');
insert into Exam (exam_id, student_id, score) values ('40', '1', '60');
insert into Exam (exam_id, student_id, score) values ('40', '2', '70');
insert into Exam (exam_id, student_id, score) values ('40', '4', '80');

解题思路

这道题难度并不大，就是逻辑有点绕。一不小心，容易出现逻辑漏洞。

根据题目要求：取出处于中游的学生信息。中游的含义是说：任意一次测验都不是第一，也不是最后一名。

那么，从逻辑上来说，我们可以通过以下步骤计算得出结果：

**第一步**：计算出每次测试的第一名和最后一名；

**第二步**：找出曾经有过第一名或最后一名的学生；

**第三步**：在所有参加测试的学生中，排除第二步中的学生；

**第四步**：根据第三步的学生，关联出学生姓名，然后排序返回；

上面的每一步，实现起来都不难。这就是前面强哥说的，这道题的难度并不大。

但梳理出上面的计算逻辑，是需要一点细心的。

强哥第一次，就在第二步和第三步上栽了。我使用下面的SQL，取出的第三步的结果：

with 
tmp1 as (
    select
        a.student_id,
        a.score,
        a.exam_id,
        rank() over(partition by a.exam_id order by a.score asc) score_a,
        rank() over(partition by a.exam_id order by a.score desc) score_d
    from Exam a
)
select 
    distinct a.student_id
from tmp1 a
where a.score_a <> 1
  and a.score_d <> 1;

结果发现，上面的SQL返回了student_id=3的同学。原因是该同学在测验30中排名第2（总共有3名同学）。

而实际上，该同学在测验10中排名第1，不符合题目要求的条件。

参考SQL

未特别说明的情况下，参考SQL为基于MySQL8.0实现。

with 
tmp1 as (
    select
        a.student_id,
        a.score,
        a.exam_id,
        rank() over(partition by a.exam_id order by a.score asc) score_a,
        rank() over(partition by a.exam_id order by a.score desc) score_d
    from Exam a
),
tmp2 as (
    select 
        distinct a.student_id
    from Exam a
    where a.student_id not in 
        (   select 
                b.student_id
            from tmp1 b
            where b.score_a = 1
               or b.score_d = 1
        )
)
select
    a.student_id,
    b.student_name
from tmp2 a
inner join Student b
on a.student_id = b.student_id
order by a.student_id;