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

-- 各个职业对应的薪水中位数排名
SELECT
	job,
	floor((count(g.job)+ 1)/ 2),
	CEIL((count(g.job)+ 1)/ 2)
FROM
	grade g
GROUP BY
	g.job
ORDER BY
	g.job ASC;
-- 各个职业薪水排名
SELECT id,job,score,DENSE_RANK() over(PARTITION BY job ORDER BY score desc) t
FROM grade g;

-- 组合查询出最终结果
SELECT 
tmp2.*
FROM 
(SELECT
	job,
	floor((count(g.job)+ 1)/ 2) fl,
	CEIL((count(g.job)+ 1)/ 2) ce
FROM
	grade g
GROUP BY
	g.job
ORDER BY
	g.job ASC) tmp1,
	(SELECT id,job,score,DENSE_RANK() over(PARTITION BY job ORDER BY score desc) t_rank
FROM grade g) tmp2
WHERE tmp1.job = tmp2.job
AND (tmp1.fl = tmp2.t_rank OR tmp1.ce = tmp2.t_rank)
ORDER BY tmp2.id asc