描述:
表:
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:
输入: 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 | +------------+--------------+-----------+ 输出: +-------------+---------------+ | student_id | student_name | +-------------+---------------+ | 2 | Jade | +-------------+---------------+ 解释: 对于测验 1: 学生 1 和 3 分别获得了最低分和最高分。 对于测验 2: 学生 1 既获得了最高分, 也获得了最低分。 对于测验 3 和 4: 学生 1 和 4 分别获得了最低分和最高分。 学生 2 和 5 没有在任一场测验中获得了最高分或者最低分。 因为学生 5 从来没有参加过任何测验, 所以他被排除于结果表。 由此, 我们仅仅返回学生 2 的信息。
数据准备:
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)
Truncate table Student
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')
Truncate table Exam
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')
分析:
①首先先找出每个科目的最高分和最低分
select exam_id, student_id, score, max(score) over (partition by exam_id ) as max, min(score) over (partition by exam_id ) as min from exam
②找出是某科最高分或最低分的学生(成绩不是中游的学生)
select distinct student_id from (select exam_id, student_id, score, max(score) over (partition by exam_id ) as max, min(score) over (partition by exam_id ) as min from exam)t1 where score = max or score = min
③将两个表进行内连接 这样就过滤掉了学生表中有信息但未参加考试的学生 在合并表中用not in筛选查询成绩为中游的学生 排序
代码:
select distinct exam.student_id,student_name
from exam,
student
where exam.student_id = student.student_id
and exam.student_id not in (
select distinct student_id
from (select
student_id,
score,
max(score) over (partition by exam_id ) as max,
min(score) over (partition by exam_id ) as min
from exam)t1
where score = max
or score = min
)
order by student_id
总结:
如果直接查成绩为中游的学生 那么可能出现 A同学在科目1 是最高分 在科目2中是中游 查询将不准确 所以使用not in取反