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

纪念一下，用最清晰的思路和最复杂的解法算出答案

先算每个成绩的最差排名:

with zc as(
    select grade,sum(number)over(order by grade) g
from class_grade
order by grade),

再算每个成绩的最好排名：

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

再算中位数对应的位数，注意如果总人数为奇数，会输出两个相同的答案，如果是偶数则不同：

zw as(
select case when sum(number)%2=1 then round(sum(number)/2,0)
    else round(sum(number)/2,0) end w1,
    case when sum(number)%2=1 then round(sum(number)/2,0)
    else round(sum(number)/2,0)+1 end w2
    from class_grade
),

再算上一步中第一个结果对应的成绩

yw as(
    select grade from zh
    where g<=(select w1 from zw)
    order by grade desc
    limit 1
     ),

以及第二个结果对应的成绩，如果总数是奇数，这两步会输出相同的结果

ew as(
    select grade from zc
    where g>=(select w2 from zw)
    order by grade
    limit 1
     )

最后将上述所有步骤合并

with zc as(
    select grade,sum(number)over(order by grade) g
from class_grade
order by grade),
zh as(
select grade,sum(number)over(order by grade)-number+1 g
    from class_grade),
zw as(
select case when sum(number)%2=1 then round(sum(number)/2,0)
    else round(sum(number)/2,0) end w1,
    case when sum(number)%2=1 then round(sum(number)/2,0)
    else round(sum(number)/2,0)+1 end w2
    from class_grade
),
yw as(
    select grade from zh
    where g<=(select w1 from zw)
    order by grade desc
    limit 1
     ),
ew as(
    select grade from zc
    where g>=(select w2 from zw)
    order by grade
    limit 1
     )

SELECT distinct grade from class_grade
where grade in (select * from yw)
or grade in (select * from ew)
order by grade