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

select a.id ,b.job,a.score,a.temprank as t_rank From 
(select id,job,score,row_number()over(partition by job order by score desc) as temprank From grade)as a Join
(select job,count(score) as num From grade Group by job)as b On
a.job=b.job 
Where
(case
when b.num%2=1 then a.temprank=(1+b.num)/2
else a.temprank=b.num/2 or a.temprank=1+b.num/2 end)
Order By a.id