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

# 看总数 总数%2=1 中位数就是len/2 和 len/2+1 
#        总数%2=0 中位数就是(len+1)/2
with A as (
select grade
,sum(number)over(order by grade)e
,round(len/2)d
,round(len%2)mo
from class_grade
join (
    select sum(number)len from class_grade
)a
)
# 偏移一下
,B as (
select grade
,lag(e,1,0)over(order by grade)b
,e
,d
,mo
from A
)
# 只要中位数在取值区间 直接输出
,C as (
select case when mo = 0 and (d between b and e) then grade 
when mo = 1 and (d > b and d <= e) then grade end grade
from B
)
# 最后去空 排序
select grade from C
where grade is not null
order by 1

下面的错了 不用看
# 先把class_grade repeat number次 再排序好
with A as (
select grade,repeat(grade,number)rp from class_grade order by grade
)
# 再把每一行都放在一起
,B as (
select group_concat(rp order by grade)gc from A
)
# 再把逗号去掉
,C as (
select replace(gc,',','')re
,length(replace(gc,',',''))len from B
)
# 最后再条件判断 len为奇 则为(len+1)/2
# len为偶 则为len/2 和 len/2+1
select case when len%2=0 then substring(re,len/2,1)
else substring(re,(len+1)/2,1) end from C
union
select case when len%2=0 then substring(re,len/2+1,1)
else substring(re,(len+1)/2,1) end from C