小笨蛋看过来| #最差是第几名(二)#

select
    grade,
    number,
    sum(number) over(order by grade asc) max_rank
from
    class_grade
order by 
    grade asc

select 
    a.grade,
    a.number,
    a.max_rank-a.number+1 min_rank,
    max_rank
from
    (select grade, number, sum(number) over(order by grade) max_rank from class_grade) a

select
    (case when sum(number) % 2 = 0 then round(sum(number)/2 ,0) else round((sum(number)+1)/2 ,0) end) as mid_number
from class_grade 
union  # 注意用union 而不是 union all 这样会帮忙去重，即当sum(number)是奇数时，可以只输出一个
select
    (case when sum(number) % 2 = 0 then round((sum(number)+2)/2 ,0) else round((sum(number)+1)/2 ,0) end) as mid_number
from class_grade 

select 
    distinct b.grade
from
(
    select 
        a.grade,
        a.number,
        a.max_rank-a.number+1 min_rank,
        max_rank
    from
        (select grade, number, sum(number) over(order by grade) max_rank from class_grade) a
) b
join 
(
    select
        (case when sum(number) % 2 = 0 then round(sum(number)/2 ,0) else round((sum(number)+1)/2 ,0) end) as mid_grade
    from class_grade 
    union  # 注意用union 而不是 union all 这样会帮忙去重，即当sum(number)是奇数时，可以只输出一个
    select
        (case when sum(number) % 2 = 0 then round((sum(number)+2)/2 ,0) else round((sum(number)+1)/2 ,0) end) as mid_grade
    from class_grade 
) c
on c.mid_grade between b.min_rank and b.max_rank
order by grade asc