题解 | #最差是第几名(二)#

-- 查询中位数学生位于什么档次
-- 首先需要知道中位数是哪一个或两个数字
-- 再判断中位数位于哪个sum窗口函数得到的结果范围内
with medium as(
    select
     sum(number) as total
    from
      class_grade
),
medium_next as(
    select
      case
       when mod(total, 2) = 0 then total/2 
       else ceil(total/2) 
      end as start,
      case
       when mod(total, 2)=0 then (total/2)+1
       else null
      end as end
    from
      medium
),
total_roll as(
    select
     grade,
     sum(number) over (order by grade) as rolltotal
    from
     class_grade
),
 total_roll2 as(
    select
      grade,
      rolltotal,
      lag(rolltotal,1) over (order by grade) as rolltotal2
    from
     total_roll
)
select
 grade
from 
 total_roll2, medium_next
where 
 (start >rolltotal2 and start<=rolltotal)
 or (end >rolltotal2 and end<=rolltotal)

挺意外这道题写的这么顺的～