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

再没看大神的题解之前，先自己啃了下来，虽然有点长，但是能通过哈哈哈哈。

#先将不重复的排序结果加到成绩表上，再去联结筛选出的包含中位数位置的子表
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;