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

with tmp as (select grade, (select sum(number) from class_grade c2 where c1.grade > c2.grade ) as le, (select sum(number) from class_grade c2 where c1.grade >= c2.grade ) as r from class_grade c1
order by grade asc
)

select grade from tmp
where le/(select sum(number) from class_grade) = 0.5 or r/(select sum(number) from class_grade) = 0.5 or 
((le/(select sum(number) from class_grade) < 0.5) and r/(select sum(number) from class_grade) > 0.5)

选出不包含本等级的数量为le 左边界；包含本等级的数量为r 右边界；然后有3种条件可以判定出它是中位数，1、左边界/总数在0.5上；2、右边界/总数在0.5上；3、左边界小于0.5且右边界大于0.5

更推荐下面的写法，看评论区才学到的，更省事，让窗口包含了0.5就行：

with tmp as (select grade, (select sum(number) from class_grade c2 where c1.grade > c2.grade ) as le, (select sum(number) from class_grade c2 where c1.grade >= c2.grade ) as r from class_grade c1
order by grade asc
)

select grade from tmp
where 
((le/(select sum(number) from class_grade) <= 0.5) and r/(select sum(number) from class_grade) >= 0.5)