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

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

用union:

select
s.student_id,s.student_name
from
(
select
distinct student_id
from
Exam
where 
student_id not in
(
select
s1.student_id
from
(select
student_id, max(score) over(partition by exam_id) ms,score s
from
Exam
) s1
where s1.ms = s1.s
 
union  all
 
select
s1.student_id
from
(select
student_id, min(score) over(partition by exam_id) ms,score s
from
Exam
) s1
where s1.ms = s1.s
) ) ss1 left join Student s on ss1.student_id = s.student_id
order by student_id 

select
s.student_id,s.student_name
from
(
select
distinct student_id
from
Exam
where 
student_id not in   #在考试里 不在 下面 表里的 学生id 就是 题中 需要的 
(
select
s1.student_id
from
(select
student_id, max(score) over(partition by exam_id) ms,score s  # 选出考试成绩 最大的 学生id
from
Exam
) s1
where s1.ms = s1.s
 
union  all      # 上下两表 拼接
 
select
s1.student_id
from
(select
student_id, min(score) over(partition by exam_id) ms,score s  # 选出 考试成绩 最小的 学生id
from
Exam
) s1
where s1.ms = s1.s
) ) ss1 left join Student s on ss1.student_id = s.student_id
order by student_id 

开两个窗:

select
student_id,student_name
from
Student 
where student_id in 
(
select
s1.student_id
from
(    
select
student_id,if(dense_rank() over(partition by exam_id order by score)=1,1,0) dr1,if(dense_rank() over(partition by exam_id order by score desc)=1,1,0) dr2
from
Exam
) s1
group by
s1.student_id
having sum(s1.dr1) = 0 and sum(s1.dr2) = 0
)

select
student_id,student_name
from
Student 
where student_id in 
(
select
s1.student_id #题中 需要的 学生id
from
(    
select
student_id,if(dense_rank() over(partition by exam_id order by score)=1,1,0) dr1,if(dense_rank() over(partition by exam_id order by score desc)=1,1,0) dr2    # 按score 降序 和升序 标号 标号为1的置为1 标号 为2的 置为0   (便于后期用sum为零选出想要的值)
from
Exam
) s1
group by
s1.student_id
having sum(s1.dr1) = 0 and sum(s1.dr2) = 0
)

笔记:

1窗口函数只能出现在select 和order by 字句中
2如果查询的其他部分( where,GROUP BY, having)需要窗口函数,请使用子查询,在子查询使用窗口函数
3 如果查询使用聚合或者group by 请记住窗口函数只能处理分组后的结果,而不是原始的表数据.