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

select grade
from(
    select grade,
  		#本等级第一名排名=上一等级最后一名排名+1，A等级为1
  		if(t_rank_last is null,1,t_rank_last+1) t_rank_min,
  		t_rank_max,
  		#中位数最小排名
        if(all_stu % 2 = 0,all_stu/2,(all_stu+1)/2) mid_min,
  		#中位数最大排名
        if(all_stu % 2 = 0,all_stu/2 + 1,(all_stu+1)/2) mid_max
    from(
        select grade,
      		#上一等级的最后一名排名
      		sum(number) over(order by grade rows between unbounded preceding and 1 preceding) as t_rank_last,
      		#本等级最后一名排序
            sum(number) over(order by grade) t_rank_max,
      		#本班学生数
            sum(number) over() all_stu
        from class_grade
    ) t1 
) t2 
#筛选出中位数排名在本等级排名范围内的行
where t_rank_max >= mid_min and t_rank_min <= mid_max;