题解 | 考试分数(五)

with p as (
    select id,job,score,row_number() over(partition by job order by score desc) as t_rank,
    count(*) over (partition by job) as total
    from grade
)


select id,job,score,t_rank
from p
where t_rank between total/2 and total/2+1
order by id

注意这里的select 了t_rank 而不是avg（t_rank)