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

首先是中位数的取法（13个人中位数是7，12个人中位数是6.5）

(select (sum(number)+1)/2
from class_grade) zhong_wei_shu

再求每个grade的排名范围，start到end是当前grade的排名范围

(select grade
,sum(number)over(order by grade)-number+1 as start
,sum(number)over(order by grade) as end
from class_grade) new

第一种情况是中位数在某一个grade的start到end的范围内（即start<=中位数<=end），只需要筛选出来这个grade就行

(
select grade
from
(select grade
,sum(number)over(order by grade)-number+1 as start
,sum(number)over(order by grade) as end
from class_grade) new
where start<=(select (sum(number)+1)/2
from class_grade)
and
end>=(select (sum(number)+1)/2
from class_grade)
)

第二种情况是中位数在某两个grade之间(比如 A：2，B：4，C：4，D：2，中位数是6.5，在B的end与C的start之间),那么先筛选出grade_1使得start>中位数且start-1<中位数，然后从class_grade中筛选出grade_1和它上面（order by grade）的一个grade。我本来想用lag函数，但是报错了，只能用很复杂的方式去不断子查询。

select grade
from (select grade
,rank()over(order by grade) psn
from class_grade) grade_psn
where psn=
(select psn
from (select grade
,rank()over(order by grade) psn
from class_grade) grade_psn
where grade in
(select grade
from
(select grade
,sum(number)over(order by grade)-number+1 as start
,sum(number)over(order by grade) as end
from class_grade) new
where (start>(select (sum(number)+1)/2
from class_grade))
and start-1<(select (sum(number)+1)/2
from class_grade)))
or psn+1=(select psn
from (select grade
,rank()over(order by grade) psn
from class_grade) grade_psn
where grade in
(select grade
from
(select grade
,sum(number)over(order by grade)-number+1 as start
,sum(number)over(order by grade) as end
from class_grade) new
where (start>(select (sum(number)+1)/2
from class_grade))
and start-1<(select (sum(number)+1)/2
from class_grade)))

这两种情况是相斥的，若某一种能返回值则另外一种返回null，最后只需要将两种情况union即可。

select grade from
(
select grade
from
(select grade
,sum(number)over(order by grade)-number+1 as start
,sum(number)over(order by grade) as end
from class_grade) new
where start<=(select (sum(number)+1)/2
from class_grade)
and
end>=(select (sum(number)+1)/2
from class_grade)
) aaa
union
(
select grade
from (select grade
,rank()over(order by grade) psn
from class_grade) grade_psn
where psn=
(select psn
from (select grade
,rank()over(order by grade) psn
from class_grade) grade_psn
where grade in
(select grade
from
(select grade
,sum(number)over(order by grade)-number+1 as start
,sum(number)over(order by grade) as end
from class_grade) new
where (start>(select (sum(number)+1)/2
from class_grade))
and start-1<(select (sum(number)+1)/2
from class_grade)))
or psn+1=(select psn
from (select grade
,rank()over(order by grade) psn
from class_grade) grade_psn
where grade in
(select grade
from
(select grade
,sum(number)over(order by grade)-number+1 as start
,sum(number)over(order by grade) as end
from class_grade) new
where (start>(select (sum(number)+1)/2
from class_grade))
and start-1<(select (sum(number)+1)/2
from class_grade)))
)