每日一题-91（查找成绩处于中游的学生）

题91：

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

其中：

• Student表：student_id 是该表主键，student_name 学生名字.
• Exam 表：exam_id, student_id是该表主键，
  学生 student_id 在测验 exam_id 中得分为 score。

解题思路：
（1）利用case when 加窗口函数计算出每科的第一名和最后一名，如果，第一名就+1，最后一名也加1，如果，相加都是0就属于中等；
（2）在进行按照学生学号进行分组，求出中等生的学生学号，连接student表得到答案。

select  s.*
from (
    select *,
        (case when dense_rank() over(partition by exam_id order by score )=1  then 1 else 0 end) as min ,
        (case when dense_rank() over(partition by exam_id order by score desc)=1  then 1 else 0 end) as max
    from exam
) as t  left join student s on s.student_id = t.student_id
group by t.student_id
having sum(min)=0 and sum(max)=0
order by s.student_id;