法一:
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,这是个当且仅当的关系。