LeetCode（数据库）- 查找成绩处于中游的学生

-- 解决方案(1)
select student_id, student_name 
from student
where student_id not in
    (select student_id
    from exam e
    where score >= all(select score from exam where exam_id=e.exam_id)
        or score <= all(select score from exam where exam_id=e.exam_id))
and student_id in (select student_id from exam)
-- 解决方案(2)
WITH t AS(SELECT student_id, RANK() OVER(PARTITION BY exam_id ORDER BY score) rk FROM Exam
UNION ALL
SELECT student_id, RANK() OVER(PARTITION BY exam_id ORDER BY score DESC) rk FROM Exam)
SELECT DISTINCT student_id, student_name
FROM Exam JOIN Student USING(student_id)
WHERE student_id NOT IN (SELECT student_id FROM t WHERE rk = 1)
ORDER BY student_id