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

-- 使用CTE（Common Table Expression）创建一个临时表t
with t as (
    -- 从class_grade表中选择grade列
    select grade,
           -- 计算班级总人数
           (select sum(number) from class_grade) as total,
           -- 按grade升序累加人数
           sum(number) over(order by grade asc) as a,
           -- 按grade降序累加人数
           sum(number) over(order by grade desc) as b
    from class_grade
)

-- 查询中位数所在的grade
select grade
from t
-- 条件：累加人数a和b都大于等于总人数的一半
where a >= total / 2 and b >= total / 2
-- 按grade升序排序
order by grade;