题解 | 考试分数(五)

# 按照语言进行分组，并排序
# 找出中位数的序号，并将序号内连接到原始表格
# 再次分组排序
select 
    c.id as id, 
    c.job as job,
    c.score as score,
    c.ranks as t_rank
from
    (select
        a.job as job,
        case when mod(count(*),2) = 1 then round(ceil(count(*)/2) )
            when mod(count(*),2) = 0 then round(count(*)/2) end as start,
        case when mod(count(*),2) = 1 then round(ceil(count(*)/2) )
            when mod(count(*),2) = 0 then round(count(*)/2 + 1) end as end    
    from 
        (select
        *,
        dense_rank() over(partition by job order by score desc) as ranks
        from
        grade) a 
    group by 
        a.job) b 
inner join
    (select
        *,
        dense_rank() over(partition by job order by score desc) as ranks
        from
        grade) c
on 
    b.job = c.job
where 
    c.ranks in (b.start,b.end)
order by
    id asc;