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

MySQL: 不使用rank() over()

我们结合考试分数四可以得出各个岗位分数的中位数位置范围，那么按照题意我们只需要求出这个中数范围上的grade信息即可。

中位数范围：

select job, floor((count(job) + 1)/2) as start, ceiling((count(job) + 1)/2) as end
from grade
group by job
order by job

那么如何求出这个中位数范围对应的grade信息呢，我们在考试分数三已经求得了各个岗位的分数排名情况，那我们只需要保证分数排名在中位数范围内即可。

分数排名代码：

select g1.id, l.name, g1.score
from grade g1 join language l on g1.language_id=l.id 
where 
(
    select count(distinct g2.score) 
    from grade g2 
    where g2.score>=g1.score and g1.language_id=g2.language_id
) <=2 order by l.name,g1.score desc ,g1.id;

联立两个查询得出最终的题解：

select B.* from
(SELECT job,FLOOR((COUNT(*)+1)/2) AS `start`, CEILING((count(job) + 1) / 2) AS `end` 
FROM grade  GROUP BY job) A

JOIN

(select g1.*, (
    select count(distinct g2.score) 
    from grade g2 
    where g2.score>=g1.score and g1.job=g2.job) as t_rank
from grade g1 ) B

on (A.job=B.job  and B.t_rank between A.start and A.end)
order by B.id