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

select
    b.id,b.job,b.score,b.r_n
FROM 
#先找出中位数的序号，使用75题方法
    (SELECT
        id,
        job,
        floor((count(*)+1)/2) 'start',
        floor((count(*)+2)/2) 'end'
    FROM
        grade
    group by job
    order by job
    ) a
join
#使用开窗函数给每个排排序
    (SELECT
        *,ROW_NUMBER() over(partition by job order by score desc) r_n
    FROM
        grade
    ) b on a.job=b.job
where
    b.r_n = a.start or b.r_n=a.end
group by b.id
order by b.id