位置上的所有 | #考试分数(五)#

with tt as(
select job,score,t_rank
from
(select id,job,score,dense_rank()over(partition by job order by score desc) t_rank,row_number()over(partition by job order by score desc) px,round(count(id)over(partition by job)/2) s,round((count(id)over(partition by job)+1)/2) e
from grade) t
where px = s or px = e)

select grade.id,grade.job,grade.score,tt.t_rank
from grade,tt
where grade.job = tt.job and grade.score = tt.score
group by grade.id
order by grade.id