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

select t.id,
t.job,
t.score,
t.t_rank
from (select id,
              job,
              score,
             count(id) over(partition by job) cnt,
             dense_rank() over(partition by job order by score) a, 
             dense_rank() over(partition by job order by score desc) t_rank
              from grade g1
             ) t
where a>=cnt/2 and t_rank>=cnt/2
order by t.id

• 答案解析

 dense_rank() over(partition by job order by score) a, 
 dense_rank() over(partition by job order by score desc) t_rank

有很多人在这里使用row_number()或者rank(),我觉得是不正确的,本题中只是数据没有相同的,如果有相同的数据,这样求得的中位数是不正确的,需要使用dense_rank()