leetcode-1412.查询成绩处于中游的学生(困难级)

功与名861

已于 2024-08-01 09:24:58 修改

阅读量1.1k

点赞数 36

文章标签： leetcode 数据库算法

于 2024-07-29 21:00:59 首次发布

本文链接：https://blog.csdn.net/qq_62757927/article/details/140780220

版权

题目：

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

创建数据库：

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')

Student表：

Exam表：

要求：

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

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

返回结果表按照 student_id 排序。

对题目进行分析：

对题目从头到尾进行解读。

首先成绩处于中游的学生至少参加一次测试。这个信息可以忽略不记，因为如果没有测试，在 Exam表中就没有没有测试的学生。最后的得出符合要求的学生id时候与 Student表进行关联时也不会关联到没测试的学生。

其次，得到所有得分既不是最高分也不是最低分的学生，我们首先要求出最高分和最低分，那么我们可以通过窗口函数得到最低分和最高分，放在我们的表的最后面，然后通过筛选让我们的分数不等于他们可以了，那么我们在这里也应该想到如何确定筛选出来的学生其他科目是不是处于中等水平（我们需要的是所有学科都处于中等的水平。举例：我有4个学科但我有1个学科出现最高分或者最低分我们可以把它筛选出去，那我们如何把他其他的考的科目剔除），这时候我们需要通过窗口函数得出这个学生总共考了几门科目，筛选后有几门科目。让这两个数据相同就能得到想要的学生。

分析完之后，那么让我们说干就干。

解题：

第一步：得到每个科目的最大分数和最小分数（通过聚合函数）

select exam_id, 
       max(score) maxs,
       min(score) mins 
from exam group by exam_id

第二步：关联两表，方便筛选。统计每人考试科目的数量。

我们得到每个科目的最大分数和最小分数，无法直接通过这个表来直接判断每个人每个科目是否为最高分或者最低分，这时候我们通过关联会更清晰的进行判断。关联时候我们可以把上面筛选的表当作临时表与Exam表进行关联，然后通过窗口函数对学生id进行分组得到每个人考了几门科目。

with t1 as (select exam_id,
                   max(score) maxs,
                   min(score) mins
            from exam group by exam_id)
select exam.exam_id,
       student_id,
       exam.score,
       maxs,
       mins,
       count(student_id) over (partition by student_id) cot
FROM exam
         join t1 on t1.exam_id = exam.exam_id
order by student_id;

通过上面的图片我们可以清晰的知道怎么判断。

第三步：筛选，计筛选后每人有几个科目。

with t1 as (select exam_id,
                   max(score) maxs,
                   min(score) mins
            from exam
            group by exam_id),
     t2 as (select exam.exam_id,
                   student_id,
                   exam.score,
                   maxs,
                   mins,
                   count(student_id) over (partition by student_id) cot
            FROM exam
                     join t1 on t1.exam_id = exam.exam_id
            order by student_id)
select *, count(student_id) over (partition by student_id) jishu
from t2
where score != maxs
  and score != mins

第四步：判断每人考试科目总数和筛选出来之后考试科目的数量。

with t1 as (select exam_id,
                   max(score) maxs,
                   min(score) mins
            from exam
            group by exam_id),
     t2 as (select exam.exam_id,
                   student_id,
                   exam.score,
                   maxs,
                   mins,
                   count(student_id) over (partition by student_id) cot
            FROM exam
                     join t1 on t1.exam_id = exam.exam_id
            order by student_id),
     t3 as (select *, count(student_id) over (partition by student_id) jishu
            from t2
            where score != maxs
              and score != mins)
select distinct student_id
from t3
where cot = jishu

第五步：通过子查询得到那个学生是中游学生

select *
from exam;
select *
from student
where student_id in (with t1 as (select exam_id,
                                        max(score) maxs,
                                        min(score) mins
                                 from exam group by exam_id),
                          t2 as (select exam.exam_id,
                                        student_id,
                                        exam.score,
                                        maxs,
                                        mins,
                                        count(student_id) over (partition by student_id) cot
                                 FROM exam
                                          join t1 on t1.exam_id = exam.exam_id
                                 order by student_id),
                          t3 as (select *, count(student_id) over (partition by student_id) jishu
                                 from t2
                                 where score != maxs and score != mins)
                     select distinct student_id
                     from t3
                     where cot = jishu)
;