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

SELECT
id,
job,
score,
t_rank

FROM(
    SELECT
    *,
    DENSE_RANK() OVER(PARTITION BY job ORDER BY score DESC) AS t_rank,
    COUNT(*) OVER(PARTITION BY job) AS num

    FROM grade) AS t1

WHERE 
CASE WHEN num%2 = 0 THEN t_rank BETWEEN FLOOR((num + 1)/2) AND CEILING((num + 1)/2)
     ELSE t_rank = num/2 + 0.5
END
    
ORDER BY id