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

SELECT t.id,t.job,t.score,t.rw t_rank
FROM (
	SELECT *,
	ROW_NUMBER() over(PARTITION by job ORDER BY score DESC) rw
	FROM grade 
) t JOIN (
	SELECT job,
	FLOOR((1 + COUNT(*)) / 2) low,
	CEIL((1 + COUNT(*)) / 2) high
	FROM grade
	GROUP BY job
) t1 ON t.job = t1.job 
WHERE t.rw BETWEEN t1.low AND t1.high
ORDER BY t.id;