##1,首先将每门的最高分和最低分查出来,叫做max_min
##2,将所有考的每门的最高和最低分查出来,叫做max_min_id
##3,然后将student表和exam表内联。
##4,过滤的条件是,学生的id没在刚刚查出来的表max_min_id里
##5,最后排序。
with max_min as (
select exam_id , max(score) from exam group by exam_id
union
select exam_id, min(score) from exam group by exam_id),
max_min_id as (
select Student_id from exam where (exam_id,score ) in (
select * from max_min )
)
select distinct e.Student_id,s.Student_name from
Student s join exam e on s.Student_id=e.Student_id
where e.Student_id not in (
select * from max_min_id )
order by e.student_id