#1412.查找成绩处在中游的学生

这题要求成绩处于中游的学生,指至少参加一次测验得分不是最高也不是最低的学生,即参加测试没有最高和最低得分的学生,所以,可以使用开窗函数,对Exam表按照exam_id分组,max()函数和min()函数分别开窗出一列,记录每次测试的最高分与最低分,即

select *,

max(score) over(partition by exam_id ) m1,

min(score) over(partition by exam_id) m2 from exa

再条件筛选出得分和最高得分或者与最低得分相同的学生student_id,对于Exam表进行筛除,这些学生的student_id,剩下的student_id就是中游学生,再与Stduent表相连接,就可得知这些学生名字字段,即结果

with a as

(select *,

max(score) over(partition by exam_id ) m1,

min(score) over(partition by exam_id) m2 from exam),

b as

(select distinct student_id from exam where student_id not in

(select distinct student_id from a where score=m1 or score=m2))

select b.student_id,student_name from  b left join student s on b.student_id=s.student_id

order by b.student_id;