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

SELECT 
    t1.id,
    t1.job,
    t1.score,
    t1.medium
FROM
    (SELECT
        *,
        RANK() OVER (PARTITION BY job ORDER BY score desc) as t_rank,
        CASE
            WHEN (COUNT(*) OVER (PARTITION BY job)) % 2 = 1 THEN ROUND((COUNT(*) OVER (PARTITION BY job) + 1) / 2,0)
            WHEN (COUNT(*) OVER (PARTITION BY job)) / 2 = (RANK() OVER (PARTITION BY job ORDER BY score desc))
            THEN RANK() OVER (PARTITION BY job ORDER BY score desc)
            WHEN (COUNT(*) OVER (PARTITION BY job)) / 2  + 1 = (RANK() OVER (PARTITION BY job ORDER BY score desc))
            THEN RANK() OVER (PARTITION BY job ORDER BY score desc)
        END AS medium
    FROM
        grade
    ) t1
where 
    t1.t_rank = t1.medium
order by
    t1.id