考试分数（5）：查询各岗位分数的中位数位置上的所有grade信息

去网上搜了一下答案，当总数为偶数时，两个数的逆序或者顺序都会差1，使用abs()函数求绝对值；
这里还需要使用Cast是因为不转换的话，可能序列号太大超出上限，太小超出下限0，从而报错
修改一下代码：

select id,a.job,score,t_rank
from(
select id,job,score,
cast(row_number() over (partition by job order by score desc) as signed) as t_rank,
cast(row_number() over (partition by job order by score)as signed) as a_rank
from grade
) as a
join
(select job,count(id) as cnt
from grade
group by job) as b
on a.job=b.job
where abs(a.t_rank-a.a_rank)=1 or a.t_rank=a.a_rank
order by id

————————
第二次的答案：
这样写的答案居然对了，先mark一下，回头再来看
中位数为正序、逆序均大于等于整个序列数的一半

select id,a.job,score,t_rank
from(
select id,job,score,
row_number() over (partition by job order by score desc) as t_rank,
row_number() over (partition by job order by score) as a_rank
from grade
) as a
join 
(select job,count(id) as cnt
from grade
group by job) as b
on a.job=b.job
where a.t_rank>=cnt/2 and a.rank>=cnt/2
order by id

————————
第一次的答案：
分为3个步骤：

1. 找到每个用户每科成绩的排名；
2. 找到中位数的取值范围；
3. 将排名和中位数比较；

计算排名使用窗口函数dense_rank

SELECT id,job,score,
    DENSE_RANK() OVER (PARTITION BY job ORDER BY score DESC) AS r
    FROM grade

使用case找到中位数的取值范围

SELECT job,
    (CASE WHEN cnt%2=1 THEN (cnt+1)/2 ELSE (cnt/2) END) AS start,
    (CASE WHEN cnt%2=1 THEN (cnt+1)/2 ELSE (cnt/2+1) END) AS end
     FROM (
         SELECT job,COUNT(id) AS cnt
         FROM grade
         GROUP BY job)AS d

将上面两步联合起来，增加排名和中位数比较的条件（or）

SELECT b.id,b.job,b.score,b.r
FROM (
    SELECT job,
    (CASE WHEN cnt%2=1 THEN (cnt+1)/2 ELSE (cnt/2) END) AS start,
    (CASE WHEN cnt%2=1 THEN (cnt+1)/2 ELSE (cnt/2+1) END) AS end
     FROM (
         SELECT job,COUNT(id) AS cnt
         FROM grade
         GROUP BY job)AS d) AS a
JOIN 
    (SELECT id,job,score,
    DENSE_RANK() OVER (PARTITION BY job ORDER BY score DESC) AS r
    FROM grade) AS b
ON a.job=b.job
WHERE b.r=a.start OR b.r=a.end
ORDER BY b.id