SELECT
id,
job,
score,
dn rank
FROM
(
SELECT
id,
job,
score,
rank () over ( PARTITION BY job ORDER BY score ) rn,--正序位置 rank () over ( PARTITION BY job ORDER BY score DESC ) dn,--倒序位置 count() over ( PARTITION BY job ) cnt --参与排序总数
FROM
grade
)
WHERE
( cnt % 2 = 1 AND rn = dn ) -- 若排序总数为奇数,则中位数的正序位置与倒序位置相等
OR ( cnt % 2 = 0 AND abs( rn - dn )= 1 ) --若排序总数为偶数,则中位数的正序位置与倒序位置正好差 1
ORDER BY
id