select
    b.id,
    c.job,
    b.score,
    b.t_rank
from
    (
        select
            a.job,
            ceil(count_ / 2) start,
            ceil((count_+1) / 2) end
        from
            (
                select
                    job,
                    count(*) count_
                from
                    grade
                group by
                    job
            ) a
) c
inner join (
    select
        id,
        score,
        job,
        row_number() over (
            partition by
                job
            order by
                score desc
        ) t_rank
    from
        grade
) b 
on c.job = b.job
where
    b.t_rank = c.start
    or b.t_rank = c.end
order by b.id,b.score
  • 利用ceil找出中位数
  • 利用窗口函数得到排名
  • 连接两个表，筛选中位数位置上的信息