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

(
SELECT id,job,score,
row_number()over(PARTITION BY job ORDER BY score ASC ) AS rank_asc,
row_number()over(PARTITION BY job ORDER BY score DESC) AS rank_desc,
COUNT(*)over(PARTITION BY job) AS num_
FROM grade)

SELECT id,job,score,rank_desc AS t_rank
FROM new_table 
WHERE rank_asc >= num_/2 AND rank_desc >= num_/2
ORDER BY id ;