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

select id,job,score,t_rank from( SELECT t1.id,t1.job,t1.score,t1.t_rank,t2.cnt, case when mod(t2.cnt,2)=1 then round((t2.cnt+1)/2,0) when mod(t2.cnt,2)=0 then round((t2.cnt+1)/2,1) else null end med from( SELECT *,RANK() over(PARTITION by job order by score desc) as t_rank from grade)t1 LEFT JOIN (SELECT job,COUNT(id) as cnt from grade GROUP by job)t2 using(job) )t where t_rank=med or t_rank=ceil(med) or t_rank=floor(med) order by id ;