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

if函数的输出参数可以是表达式

select id,t1.job,score,t_rank from
(select id,job,score,dense_rank()over(partition by job order by score desc)t_rank
from grade)t1 
join 
(select job,count(id) m from grade group by job)t2
on t1.job=t2.job and if(t2.m%2=1,t_rank=(t2.m+1)/2,t_rank in (t2.m/2,t2.m/2+1))
order by id

也可以用count窗口函数，不用group by+join

其他方法，无论奇偶，中位数的位置距离（个数+1）/2 小于1：

select id,job,score,s_rank
from 
(select *
        ,(row_number()over(partition by job order by score desc))as s_rank
	    ,(count(score)over(partition by job))as num
        from grade)t1
where abs(t1.s_rank-(t1.num+1)/2)<1
order by id;