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

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
    ) t1
where a >= total / 2 and b >= total / 2
order by grade;

某一序列的正序和逆序均大于整个序列的数字个数的一半即为中位数

奇数：A A B B C C D，以下取中位数为B

正序：1 2 3 4 5 6 7 -> 4 5 6 7（B C C D）

逆序：7 6 5 4 3 2 1 -> 7 6 5 4（A A B B）

偶数：A A B B C C D D，以下取中位数为B，C

正序：1 2 3 4 5 6 7 8 -> 4 5 6 7 8（B C C D D）

逆序：8 7 6 5 4 3 2 1 -> 8 7 6 5 4（A A B B C）

计算正序、逆序

sum(number) over (order by grade) a,
            sum(number) over (order by grade desc) b

取正序、逆序 >= 一半

a >= total / 2 and b >= total / 2

完整代码如下：

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
    ) t1
where a >= total / 2 and b >= total / 2
order by grade;