题解 | #考试分数(五)#
考试分数(五)
http://www.nowcoder.com/practice/b626ff9e2ad04789954c2132c74c0512
再没看大神的题解之前，先自己啃了下来，虽然有点长，但是能通过哈哈哈哈。
#先将不重复的排序结果加到成绩表上，再去联结筛选出的包含中位数位置的子表
SELECT *
FROM
(
SELECT g.id,g.job,g.score,
ROW_NUMBER() OVER(PARTITION BY g.job ORDER BY g.score DESC) AS t_rank
FROM grade g
) AS gg
WHERE (gg.job,gg.t_rank) IN
(
SELECT grade1.job,
FLOOR((SUM(1)+1)/2) AS star
FROM grade grade1
GROUP BY grade1.job
)
OR (gg.job,gg.t_rank) IN
(
SELECT grade2.job,
FLOOR((SUM(1)+2)/2) AS end
FROM grade grade2
GROUP BY grade2.job
)
ORDER BY gg.id;
顺丰集团工作强度 379人发布