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

/*
select *, rank()over(PARTITION by job order by score)
FROM grade

SELECT count(*) as c
from grade
GROUP by job
*/
SELECT t0.id, t0.job, t0.score, t0.r 
FROM (select *, rank()over(PARTITION by job order by score desc) as r
FROM grade) as t0
left JOIN (SELECT count(*) as c, job
from grade
GROUP by job) as t1
on t0.job = t1.job
WHERE t0.r = ROUND(t1.c/2) or t0.r = ROUND((t1.c+1)/2)
ORDER by t0.id;