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

/*
select sum(number)/2 as mid_num from class_grade;

select grade, sum(number) over(order by grade) as t_rank
from class_grade;

select grade, sum(number) over(order by grade desc) as f_rank
from class_grade;
*/
select a.grade
from 
    (select grade, sum(number) over(order by grade) as t_rank 
     from class_grade) as a /*grade正序排序*/
left outer join
    (select grade, sum(number) over(order by grade desc) as f_rank
    from class_grade) as b /*grade降序排序*/
on a.grade = b.grade
where a.t_rank >= (select sum(number)/2 as mid_num from class_grade)
and b.f_rank >= (select sum(number)/2 as mid_num from class_grade)
order by a.grade;

只要想通一个点，问题就不困难。

所谓中位数就是正序排序大于等于平均数的同时降序排序也大于等于平均数的数。