题解 | #考试分数(五)#
考试分数(五)
http://www.nowcoder.com/practice/b626ff9e2ad04789954c2132c74c0512
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