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

with
    t1 as (
        select *,
               row_number() over (partition by job order by score desc ) as t_rank,
               count(*) over (partition by job)                          as total
        from
            grade
    ) #用窗口函数构建新表，添加两列t_rank和total总数
select
    id,
    job,
    score,
    t_rank
from
    t1
where
      total % 2 = 1
  and t_rank = round(total / 2, 0) #总数为奇数
union all
select
    id,
    job,
    score,
    t_rank
from
    t1
where
      total % 2 = 0
  and (t_rank = round(total / 2, 0) or t_rank = round(total / 2 + 1, 0))#总数为偶数
order by
    id