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

SELECT id,job, score, t_rank FROM (SELECT a.id, a.job, a.score, RANK() OVER (PARTITION BY job ORDER BY score DESC) t_rank,b.start,b.end FROM grade a

LEFT JOIN

(SELECT job, CASE WHEN MAX(t)%2=0 THEN ROUND(MAX(t)/2+1,0) ELSE CEILING(MAX(t)/2) END start, CASE WHEN MAX(t)%2=0 THEN ROUND(MAX(t)/2,0) ELSE CEILING(MAX(t)/2) END end FROM (SELECT id, job, score, RANK() OVER (PARTITION BY job ORDER BY score ASC) t FROM grade) c

GROUP BY job) b

ON a.job=b.job) d WHERE t_rank=start OR t_rank =end

ORDER BY id ASC