题解 | #考试分数(五)#

易错点：两表格连接，where条件与on条件混淆

select grade.* from(
#对成绩按科目进行排序
select 
grade.id,grade.job,grade.score,ROW_NUMBER()over(partition by job order by score desc) as t_rank
from grade) grade
left join 
(select a.job,round(if(mod(c,2)<>0,(c+1)/2,c/2),0) as start,
round(if(mod(c,2)<>0,(c+1)/2,c/2+1),0) as end
from
#中位数位置表
(select job,count(*) c
from grade 
group by job ) a 
order by a.job asc )b 
on grade.job=b.job
#两个表格连接后根据中位数位置进行筛选
where  (grade.t_rank=b.start or grade.t_rank=b.end)
order by id asc