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

SELECT t1.id, t1.job, t1.score, t1.t_rank
FROM (
         SELECT g.*, DENSE_RANK() OVER (PARTITION BY g.job ORDER BY g.score DESC) t_rank
         FROM grade g
     ) t1,
     (
         SELECT g.job, FLOOR((COUNT(g.job) + 1) / 2) start, FLOOR((COUNT(g.job) + 2) / 2) end
         FROM grade g
         GROUP BY g.job
     ) t2
WHERE t1.job = t2.job
  AND t1.t_rank IN (t2.start, t2.end)
ORDER BY t1.id ASC;