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

sql分解步骤，在之前的步骤之上进行排序即可

SELECT k.id,k.job,k.score,k.number AS t_rank
FROM
(
SELECT 
id,job,score,
RANK() OVER(PARTITION BY job ORDER BY score DESC,job) AS number
FROM grade
)k,
(
SELECT a.job, 
CASE (MIN(a.number) + MAX(a.number))%2
    WHEN 0 THEN
        ROUND((MIN(a.number) + MAX(a.number))/2,0)
    ELSE
        FLOOR((MIN(a.number) + MAX(a.number))/2)
END start,
CASE (MIN(a.number) + MAX(a.number))%2
    WHEN 0 THEN
        ROUND((MIN(a.number) + MAX(a.number))/2,0)
    ELSE
        CEIL((MIN(a.number) + MAX(a.number))/2)
END end
FROM
(
SELECT 
id,job,score,
RANK() OVER(PARTITION BY job ORDER BY score DESC,job) AS number
FROM grade
)a
GROUP BY
a.job
)l
WHERE
k.job = l.job
AND
(
k.number = l.start
OR
k.number = l.end
)
ORDER BY
id