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

成绩处于中游的学生是指至少参加了一次测验, 且得分既不是最高分也不是最低分的学生。

写一个 SQL 语句，找出在 所有 测验中都处于中游的学生 (student_id, student_name)。

不要返回从来没有参加过测验的学生。返回结果表按照 student_id 排序。

 

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 student_id, student_name 

from Student

where (student_id, student_name) not in (

    select s.student_id, s.student_name

    from Student s left join Exam e on s.student_id = e.student_id

    where (e.exam_id, e.score) in (

        select exam_id, max(score) score

        from Exam

        group by exam_id

        union all

        select exam_id, min(score) score

        from Exam

        group by exam_id

    ) or e.exam_id is null

    group by s.student_id

)