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

select
id, job, score, s_rank
from
    (select
    id, job, score,
    row_number() over(partition by job order by score desc) as s_rank,
    count(score) over(partition by job) as s_count
    from grade) as t
where s_count/2=s_rank or (s_count+2)/2=s_rank or (s_count+1)/2=s_rank
order by id

这个where条件是最通俗易懂的了

在题解评论区发现还有一个巧妙的判断方式，在这里记录一下。

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;