题解 | 考试分数(四)

-- 方法1
with a as(
select
    *,
    row_number() over(partition by job order by score desc,id desc) o1,
    row_number() over(partition by job order by score,id) o2,
    count(id) over(partition by job) cnt
from
    grade
)
select
    job,
    o1 start,
    o2 'end'
from a
where cnt%2 <> 0 and o1 = o2

union all

select 
    job,
    o1 start,
    o2 'end'
from a 
where cnt%2 = 0 and cast(o1 as signed) -cast(o2 as signed) = -1
order by job

-- 方法2
select
    job,
    o1 start,
    o2 'end'
from(
    select
        *,
        cast(row_number() over(partition by job order by score desc,id desc) as signed) o1,
        cast(row_number() over(partition by job order by score,id) as signed) o2,
        count(id) over(partition by job) cnt
    from
        grade
) t1
where o1 = o2 or o1-o2 = -1
order by job

-- 方法3
select
    job,
    floor((count(score)+1)/2) start,
    floor((count(score)+2)/2) end 
from grade 
group by job 
order by job