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

-- 查询各个岗位分数的中位数位置上的所有grade信息
-- 先找到中位数：rank、count
-- 判断为中位数的条件，根据条件得到信息
-- 输出id job score t_rank,并且按id升序排序
with rank_s as(
    select
      id,
      job,
      score,
      dense_rank() over (partition by job order by score desc) as t_rank,
      count(score) over (partition by job) as num
    from
      grade
),
medium as (
   SELECT distinct
      id,
      job,
      score,
      t_rank,
      CASE 
        WHEN MOD(num, 2) != 0 THEN round(CEIL(num / 2.0),0)
        ELSE round(num / 2,0)
      END AS start,
      CASE 
        WHEN MOD(num, 2) != 0 THEN round(CEIL(num / 2.0),0)
        ELSE round((num / 2) + 1,0)
      END AS end
  FROM
    rank_s
)

select
  id,
  job,
  score,
  t_rank
from
  medium
where t_rank = start or t_rank = end
order by id;