题解 | #考试分数(五)#
考试分数(五)
http://www.nowcoder.com/practice/b626ff9e2ad04789954c2132c74c0512
SELECT f.id,f.job,f.score,f.t_rank
FROM
(SELECT id,job,score,ROW_NUMBER()OVER(PARTITION BY job ORDER BY score DESC) AS t_rank
FROM grade ) AS f JOIN
(SELECT job,CASE WHEN COUNT(id)%2=0 THEN CEILING(COUNT(id)*1/2)
ELSE CEILING(COUNT(id)*1/2) END AS st,
CASE WHEN COUNT(id)%2=0 THEN CEILING(COUNT(id)*1/2+1)
ELSE CEILING(COUNT(id)*1/2) END AS ed
FROM grade
GROUP BY job
ORDER BY job) AS f1
ON f.job=f1.job
WHERE f.t_rank=f1.st
OR f.t_rank=f1.ed
ORDER BY f.id
FROM
(SELECT id,job,score,ROW_NUMBER()OVER(PARTITION BY job ORDER BY score DESC) AS t_rank
FROM grade ) AS f JOIN
(SELECT job,CASE WHEN COUNT(id)%2=0 THEN CEILING(COUNT(id)*1/2)
ELSE CEILING(COUNT(id)*1/2) END AS st,
CASE WHEN COUNT(id)%2=0 THEN CEILING(COUNT(id)*1/2+1)
ELSE CEILING(COUNT(id)*1/2) END AS ed
FROM grade
GROUP BY job
ORDER BY job) AS f1
ON f.job=f1.job
WHERE f.t_rank=f1.st
OR f.t_rank=f1.ed
ORDER BY f.id
投递腾讯等公司6个岗位 >
美团成长空间 2357人发布