牛客网刷题笔记-SQL76 考试分数(五)

SQL76 考试分数(五)

牛客每次考试完，都会有一个成绩表(grade)，如下:

第1行表示用户id为1的用户选择了C++岗位并且考了11001分
。。。
第8行表示用户id为8的用户选择了B语言岗位并且考了9999分
请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序，结果如下:

解释：
第1行表示C++岗位的中位数位置上的为用户id为2，分数为10000，在C++岗位里面排名是第2
第2，3行表示Java岗位的中位数位置上的为用户id为4,5，分数为12000,13000，在Java岗位里面排名是第2,1
第4行表示B语言岗位的中位数位置上的为用户id为7，分数为11000，在前端岗位里面排名是第2

(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round，sqlite不支持floor函数，支持cast(x as integer) 函数，不支持if函数，支持case when …then …else …end函数，sqlite不支持自定义变量)
————————————————————————————————————————————————————

select g1.*
from (select *,row_number() over (partition by job order by score desc) as t_rank
     from grade) as g1 
left join 
     (select job,round(if(max(t_rank)%2=1,avg(t_rank),floor(avg(t_rank)))) as start,
           round(if(max(t_rank)%2=1,avg(t_rank),ceiling(avg(t_rank)))) as end
      from(select *,row_number() over (partition by job order by score desc) as t_rank
           from grade) as g
      group by job) as g2
on g1.job=g2.job
where g1.t_rank=g2.start
or g1.t_rank=g2.end
order by id