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

#中位数（题目四的答案）

SELECT job,
ROUND(CASE  WHEN num % 2 = 1 THEN (num + 1) / 2 ELSE num / 2 END,0) "start" ,
ROUND(CASE  WHEN num % 2 = 1 THEN (num + 1) / 2 ELSE num / 2 + 1 END,0) "end"
FROM (SELECT job, COUNT(job) num FROM grade GROUP BY job) t
ORDER BY job

#排名（题目二的答案）

SELECT id, job, score,
dense_rank() over(PARTITION BY job ORDER BY score DESC) "t_rank"
FROM grade

#两表结合，左外连接 排名 left join 中位数

SELECT tr.id, tr.job, tr.score, tr.t_rank
FROM (SELECT id, job, score,
	dense_rank() over(PARTITION BY job ORDER BY score DESC) "t_rank"
	FROM grade) tr 
	LEFT JOIN 
	(SELECT job,
	ROUND(CASE  WHEN num % 2 = 1 THEN (num + 1) / 2 ELSE num / 2 END,0) "start" ,
	ROUND(CASE  WHEN num % 2 = 1 THEN (num + 1) / 2 ELSE num / 2 + 1 END,0) "end"
	FROM (SELECT job, COUNT(job) num FROM grade GROUP BY job) t
	ORDER BY job) tz
	ON tr.job = tz.job
WHERE tr.t_rank = tz.start OR tr.t_rank = tz.end
ORDER BY id