题解 | 考试分数(五)

select temp2.id, temp2.job, grade.score, rn as t_rank
from (
    select id, job, score, rn, (cnt+1)/2 as middle,
           case when rn=(cnt+1)/2 or rn=floor((cnt+1)/2) or rn=ceil((cnt+1)/2) then 1 else 0 end as flag
    from
        (
            select id, job, score,
                   row_number() over (partition by job order by score desc) as rn,
                   count(1) over (partition by job) as cnt
            from grade
        ) temp
     ) temp2 join grade on temp2.id=grade.id
where flag=1
order by temp2.id;