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

WITH grade_count AS (
    SELECT job, COUNT(job) AS job_cnt
    FROM grade
    GROUP BY job
),
grade_rank AS (
    SELECT id, job, score, DENSE_RANK() OVER (
    PARTITION BY job
    ORDER BY score DESC) AS d_rank
    FROM grade
    ORDER BY id ASC
)

SELECT r.id, r.job, r.score, r.d_rank
FROM grade_rank AS r
LEFT JOIN grade_count AS c
ON r.job = c.job
WHERE r.d_rank = (c.job_cnt + 1)/2
OR r.d_rank = c.job_cnt/2
OR r.d_rank = c.job_cnt/2 + 1
ORDER BY id ASC;