每场考试都处于中游的学生
LeetCode 1412 · 排除极值、双向排名、反连接
面试怎么回答
先用 RANK() OVER (PARTITION BY exam_id ORDER BY score DESC) 和升序的 RANK 找出所有在某场考试里拿过最高或最低分的学生，再从考试表里的学生中排除他们，关联姓名并排序。「每一场都满足」很难正面写，转成「任何一场都不违反」用排除法更简单。
先把这段讲顺；面试官追问时，再展开下面的原理和细节。
详细讲解
题意
学生表和考试成绩表。「中游」指在某场考试里既不是最高分也不是最低分。找出参加过考试、并且在参加的每一场考试里都处于中游的学生，按学生 ID 排序。
表结构
- Student：student_id（主键）、student_name
- Exam：exam_id、student_id、score；(exam_id, student_id) 为主键
思路
反过来想：只要在任何一场考试里拿过最高分或最低分，就不合格。先按考试分区做升序和降序两个排名，找出所有拿过第一或最后的学生；再从参加过考试的学生里排除他们。以考试表为起点自然排除了没参加过考试的学生。
参考解法
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易错点
- 从学生表出发 NOT IN，没参加过考试的学生也会被选出来，题目要求排除。
- 一场考试里并列最高或并列最低的都算极值，用 RANK。