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

select grade
FROM (SELECT grade,SUM(number) OVER(ORDER BY grade) T1,
SUM(number) OVER(ORDER BY grade DESC) T2,
(SELECT SUM(number) FROM class_grade) T3
FROM class_grade ) T4
WHERE T1>=T3/2 AND T2>=T3/2
ORDER BY grade

求中位数区间：按升序和降序双条件更准确

左累计够一半，右累计够一半，中间区间就是中位数区间

select grade

FROM (SELECT grade,SUM(number) OVER(ORDER BY grade) T1,

SUM(number) OVER(ORDER BY grade DESC) T2,

SUM(number) OVER () T3

FROM class_grade ) T4

WHERE T1>=T3/2 AND T2>=T3/2

ORDER BY grade