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

select c1.grade from
(select grade,sum(number)over(order by grade)-number+1 as first,
sum(number)over(order by grade) as last
from CLASS_GRADE)c1,  -- 找到number对应的数字的首尾
(SELECT
CASE WHEN SUM(NUMBER)%2=0 THEN SUM(NUMBER)/2 ELSE (SUM(NUMBER)+1)/2 END AS FIRST_mid,
CASE WHEN SUM(NUMBER)%2=0 THEN SUM(NUMBER)/2+1 ELSE 0 END AS LAST_mid
FROM CLASS_GRADE)c2  -- 找到中位数所在位置
where c2.first_mid between c1.first and c1.last -- 找到中位数所在的grade
or c2.last_mid between c1.first and c1.last

/*参考大神：当某一数的正序和逆序累计均大于整个序列的数字个数的一半即为中位数
select grade
from
    (select grade,
    (select sum(number) from class_grade) as total,
    sum(number)over(order by grade) a, -- 求正序
    sum(number)over(order by grade desc) b  -- 求逆序
     from class_grade
    order by grade)t
where a >= total/2 and b >= total/2  -- 正序逆序均大于整个数列数字个数的一半
order by grade;