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

法一：

SELECT * FROM
(SELECT *, ROW_NUMBER() over (partition by job order by score DESC) t_rank FROM grade) t1
WHERE (job,t_rank) IN (SELECT job,t_medium FROM
                       (SELECT job, 
                        case when COUNT(*)%2=0 then ROUND(COUNT(*)/2) 
                        else ROUND(COUNT(*)/2) end AS t_medium
                        FROM grade GROUP BY job
                        UNION 
                        SELECT job, case when COUNT(*)%2=0 then ROUND(COUNT(*)/2)+1 
                        else ROUND(COUNT(*)/2) end AS t_medium
                        FROM grade GROUP BY job) t2
                      )
ORDER BY id

想提醒的一点是，如果用不了 (job,t_rank) IN ，比如SQL server，可以用t1表和t2表内连接，on t1.job=t2.job AND t1.t_rank=t2.t_medium。

法二妙解（参考评论区）：

SELECT id,job, score,t_rank FROM
(SELECT *, ROW_NUMBER() over (partition by job order by score DESC) t_rank,
 COUNT(score) over (partition by job) num
 FROM grade) t1
WHERE ABS(t_rank-(num+1)/2)<1
ORDER BY id

原理在于(中位数-(总个数+1)/2)的绝对值小于1，这是个当且仅当的关系。