「SQL面试题库」 No_106 查找成绩处于中游的学生

今日真题

题目介绍： 查找成绩处于中游的学生 find-the-quiet-students-in-all-exams

难度困难

SQL架构

表:

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 的信息。 ```

sql
select e.student_id,student_name
from Exam e left join Student s
on e.student_id=s.student_id
where e.student_id not in(
    select student_id
    from(
        select student_id,rank() over(partition by exam_id order by score desc) rkmax, rank() over(partition by exam_id order by score ) rkmin
        from Exam 
    )t1
    where rkmax = 1 or rkmin =1
)
group by e.student_id,student_name
order by e.student_id

• 已经有灵感了？在评论区写下你的思路吧！