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

-- 先计算job排名
-- 再计算job的对应的中位数
-- 两表再关联
select
    id,
    job,
    score,
    t_rank
from
    (
        select
            *
        from
            (
                select
                    id,
                    job,
                    score,
                    dense_rank() over (
                        partition by
                            job
                        order by
                            score desc
                    ) t_rank
                from
                    grade
            ) t1
            join (
                select
                    job jobs,
                    round(count(1) / 2) start,
                    round((count(1) + 1 )/ 2) ends
                from
                    grade
                group by
                    job
            ) t2 on t1.job = t2.jobs
    ) t11
where
    (
        t_rank = start
        or t_rank = ends
    )
order by
    id