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

SELECT 
        T1.job
        ,CASE WHEN T1.RN % 2 != 0 THEN (FLOOR(T1.RN / 2) + 1)
             ELSE FLOOR(T1.RN / 2)
             END start
        ,CASE WHEN T1.RN % 2 != 0 THEN (FLOOR(T1.RN / 2) + 1)
             ELSE (FLOOR(T1.RN / 2) + 1)
             END end
FROM 
(
    SELECT 
        T.job
        ,MAX(T.RN) RN
    FROM 
    (
        SELECT 
            *
            ,ROW_NUMBER() OVER (PARTITION BY job ORDER BY score DESC) RN 
        FROM grade
    ) T
    GROUP BY T.job
) T1
;