题解 | 考试分数(五)
SELECT b1.id, b1.job, b1.score, b1.rank2 AS t_rank
FROM (
SELECT id, job, score, COUNT(*) OVER (PARTITION BY job) AS job_num,
RANK() OVER (PARTITION BY job ORDER BY score) AS rank1,
RANK() OVER (PARTITION BY job ORDER BY score DESC) AS rank2
FROM grade) AS b1
WHERE b1.rank1>=b1.job_num/2 AND b1.rank2>=b1.job_num/2
ORDER BY b1.id
