题解 | #最差是第几名(二)#又臭又长的代码来咯

# 先找到中位数的初始位置
# 注意round是四舍五入
with t1 as (
    select (case when MOD(sum(number), 2) = 0 then round(sum(number)/2, 0) else round(sum(number)/2, 0) end ) as start
    from class_grade
),
t2 as (
    select grade, number, sum(number) OVER (ORDER BY grade) as total_number
    from class_grade
),
# 找到中位数的结束位置
t5 as (
    select (case when MOD(sum(number), 2) = 0 then round(sum(number)/2, 0)+1 else round(sum(number)/2, 0) end ) as end
    from class_grade
),
t6 as (
# 找到中位数开始的成绩
    select grade
    from t2
    where total_number >= (
        select start
        from t1
    )
    limit 1
),
t7 as (
# 找到中位数结束的成绩
    select grade
    from t2
    where total_number >= (
        select end
        from t5
    )
    limit 1
)
# 结合中位数的开始以及结尾
select grade
from t6
union
select grade
from t7