题解 | #考试分数(五)#

对分数排序 with a as (select *,row_number() over(partition by job order by score desc ) ranks from grade)

select a.* from a join (select job,case when ranks%2=1 then ceil(ranks/2) 判断数量是否为奇数，为基数可以直接取到中间值 when ranks%2=0 then round(ranks/2,0)为偶数则取中间的的一个数 end ranking from (select job,max(ranks) ranks,0 as rankss from a group by job) x union 将他们拼接 select job,case when ranks%2=1 then ceil(ranks/2) when ranks%2=0 then round(ranks/2+1,0)为偶数则取中间的第二个数 end ranking from (select job,max(ranks) ranks,0 as rankss from a group by job) x) b on a.job=b.job and a.ranks=b.ranking order by a.id