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

# 写的太麻烦了！！！嘿嘿


select
a.grade
from(
select
a.grade
,lag(a.en,1,0) over(order by a.en)+1 as st
,a.en
,a.qian
,a.hou
from(
select
a.grade
,sum(a.number) over(order by a.grade asc) as en
,(select round(if(a.sum_number%2=0,a.sum_number/2,(a.sum_number+1)/2),0) from (select sum(a.number) as sum_number from class_grade a) a) as qian
,(select round(if(a.sum_number%2=0,a.sum_number/2+1,(a.sum_number+1)/2),0) from (select sum(a.number) as sum_number from class_grade a) a) as hou
from class_grade a
) a
) a
where (a.qian>=a.st and a.qian<=a.en)
or (a.hou>=a.st and a.hou<=a.en)
order by a.grade
;