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

1. 将grade表中的score按照job分组进行排序

select job ,DENSE_RANK() over (PARTITION by job order by score ) as r
from grade

2. 按照job分组进行统计来判断奇数和偶数

   2.1 如果为job的数量为奇数，则start就为 count() / 2 + 1,end 也为 count() / 2 + 1。

   2.2 如果为job的数量为偶数，则start就为 count() / 2 ,end 也为 count() / 2 + 1。

select job,CEILING(count(r)/2) as 'start',
    case r
    when count(r) % 2 = 0 then CEILING(count(r)/2  + 1)
    else CEILING(count(r)/2)
    end as 'end'
from (select job ,DENSE_RANK() over (PARTITION by job order by score ) as r
     from grade) as t
group by job
order by job;

本题虽然要求 “请你写一个sql语句查询各个岗位分数升序之后中位数位置的范围”，但是求中位数的范围和分数排序没有关系，因为如果不求出这个中位数的化，就可以不用进行分数排序。

select job,CEILING(count(*)/2) as 'start',
    case 
    when count(*) % 2 = 0 then CEILING(count(*)/2  + 1)
    else CEILING(count(*)/2)
    end as 'end'
from grade
group by job
order by job;