易错点:两表格连接,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