确实难啊写这个区间 | #最差是第几名(二)#

WITH ranked AS(
    SELECT
        grade,
        number,
        SUM(number) OVER () AS total,
        SUM(number) OVER (ORDER BY grade) AS roll
    FROM 
        class_grade
    ORDER BY 
        grade
)

SELECT 
    grade
FROM 
    ranked
WHERE 
    (total % 2 = 0 AND (((total / 2) BETWEEN (roll - number + 1) AND roll)  #偶数
        OR ((total / 2 + 1) BETWEEN (roll - number + 1) AND roll)))
    OR
    (total % 2 = 1 AND ((total + 1) / 2) BETWEEN (roll - number + 1) AND roll)  #奇数
ORDER BY
    grade