题解 | #考试分数五#

常规思路：
1.首先获取中位数的位置 （上一题） 是两列

select job,round(count(id)/2) s ,round((count(id)+1)/2) e
from grade 
group by job 

2.获取每一个分数在job内的排名（使用窗口函数rank） 是一列

select id,job,score,rank() over(partition by job 
                                      order by score desc)

3.从1表中获取每个工作的中位数位置，从2表中获取中位数的id，然后从最外层的gg表获取中位数的所有信息

select gg.id,gg.job,gg.score,g.r from grade gg,
(select id,job,score,rank() over(partition by job 
                                      order by score desc) r
from grade) g,
(select job,round(count(id)/2) s ,round((count(id)+1)/2) e
from grade 
group by job ) r 
where g.job = r.job and (g.r = r.s or g.r=r.e) and gg.id = g.id
order by gg.id

看到的大神的写法：
1.利用窗口函数获取排名信息和每组组数信息

(select *,rank() over(partition by job order by score desc) as r,
count(*) over(partition by job) as t from grade ) A 

2.巧用 round(abs(r-(t+1)/2),2)<1 获取所有中位数信息
因为所有的中位数 不管组数是基数个还是偶数个都满足这个公式（能想到太强了）
所以利用这个公式即可判断排名r 是否是中位数，将满足条件的输出即可

select id,job,score,r from 
(select *,rank() over(partition by job order by score desc) as r,
count(*) over(partition by job) as t from grade ) A 
where round(abs(r-(t+1)/2),2)<1
order by id