题解 | 考试分数(五)

SELECT b1.id, b1.job, b1.score, b1.rank2 AS t_rank
FROM (
    SELECT id, job, score, COUNT(*) OVER (PARTITION BY job) AS job_num,
    RANK() OVER (PARTITION BY job ORDER BY score) AS rank1,
    RANK() OVER (PARTITION BY job ORDER BY score DESC) AS rank2
    FROM grade) AS b1
WHERE b1.rank1>=b1.job_num/2 AND b1.rank2>=b1.job_num/2
ORDER BY b1.id