SQL编程：考试分数（五）

题目描述

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

第1行表示用户id为1的用户选择了C++岗位并且考了11001分

。。。

第8行表示用户id为8的用户选择了前端岗位并且考了9999分

请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序，结果如下:

解释：

第1行表示C++岗位的中位数位置上的为用户id为2，分数为10000，在C++岗位里面排名是第2

第2，3行表示Java岗位的中位数位置上的为用户id为4,5，分数为12000,13000，在Java岗位里面排名是第2,1

第4行表示前端岗位的中位数位置上的为用户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不支持自定义变量)

方法一：对每个分组内部进行正序和倒叙排序，若该组数量为奇数，则两次排序的位置相同，若为偶数，则两次的位置相差为1.

select 
id,
job,
score,
dn as rank
from
(
    select
    id,
    job,
    score,
    rank() over(partition by job order by score) rn,
    rank() over(partition by job order by score desc) dn,
    count() over(partition by job) num
    from
    grade
)
where
(num%2=1 and rn=dn)
or
(num%2=0 and abs(rn-dn)=1)
order by
id;

方法二：将考试分数（三）和（四）的解法合并其实就是我们要的答案。

select
b.*
from
(
    select
    job as job,
    floor((count(*)+1)/2) as 'start',
    floor((count(*)+1)/2+if(count(*)%2=1,0,1)) as 'end'
    from
    grade 
    group by
    job
) as a
inner join
(
    select
    a1.*,
    (
        select
        count(distinct a2.score) 
        from
        grade as a2
        where
        a2.score>=a1.score
        and
        a1.job=a2.job
    ) as t_rank
    from
    grade as a1
) b
on
(
    a.job=b.job
    and
    b.t_rank between a.start and a.end
)
order by
b.id;