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

看了下讨论度最高的答案，觉得是有漏洞的，下面为例。

假如A:3 B:4 C:6 D:6 total 19 正序：3 7 13 19 倒序：19 13 7 3 正序>=total/2，筛选出C、D列；倒序>=total/2，筛选出A、B列，中位数筛选不出来。

附上我自己的答案，欢迎讨论指正。

第一步，计算出按照等级顺序排列下的每一个等级下的数量区间。

    select 
    	grade,
        sum(number) over (order by grade) - number + 1 as lower,
        sum(number) over (order by grade) as upper
    from class_grade

第二步，参考了之前求中位数位置那道题的答案，求出中位数所在的位置

select 
	floor((sum(number) + 1)/2) as location1,
    floor((sum(number) + 2)/2) as location2
from class_grade) cg2

第三步，把以上两个表格join在一起

select *
from(select 
		grade,
        sum(number) over (order by grade) - number + 1 as lower,
        sum(number) over (order by grade) as upper
     from class_grade) cg1
cross join (select
                floor((sum(number) + 1)/2) as location1,
                floor((sum(number) + 2)/2) as location2
            from class_grade) cg2) tb

第四步，看中位数位置落在哪个区间，筛选出grade

select distinct grade
from(
    select *
    from(select
             grade,
             sum(number) over (order by grade) - number + 1 as lower,
             sum(number) over (order by grade) as upper
         from class_grade) cg1
    cross join (select
                    floor((sum(number) + 1)/2) as location1,
                    floor((sum(number) + 2)/2) as location2
                from class_grade) cg2) tb
where (location1 between lower and upper) or
      (location2 between lower and upper)