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

with t1 as (
    select job, 
    (case when MOD(count(score), 2) = 1 then CONVERT(count(score)/2, SIGNED INTEGER) else CONVERT(count(score)/2, SIGNED INTEGER) end ) as start,
    (case when MOD(count(score), 2) = 1 then CONVERT(count(score)/2, SIGNED INTEGER) else CONVERT(count(score)/2, SIGNED INTEGER)+1 end ) as end
    from grade
    group by job
),
t2 as (
    select id, job, score, 
    Dense_rank() OVER (PARTITION BY job ORDER BY score DESC) as t_rank
    from grade
),
t3 as (
    select t2.*
    from t1
    left join t2
    on t1.job = t2.job
    and t1.start = t2.t_rank
),
t4 as (
    select t2.*
    from t1
    left join t2
    on t1.job = t2.job
    and t1.end = t2.t_rank
)
select t3.*
from t3
union 
select t4.*
from t4
order by id;