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

select
    g.id,
    g.job,
    g.score,
    t_rank
from
    (
        select
            id,
            job,
            score,
            row_number() over (
                partition by
                    job
                order by
                    score DESC
            ) as t_rank
        from
            grade
    ) as g
    join (
        select
            job,
            floor((count(job) + 1) / 2) as t1,
            floor((count(job) + 2) / 2) as t2
        from
            grade
        group by
            job
    ) as tmp on g.job = tmp.job
where
    g.t_rank in (tmp.t1, tmp.t2)
order by
    id ASC