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