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

我就来一个通俗易懂，不需要妙解

仅需要知道case...when...then...end就好了。我是没想到它可以在where上使用，TQL

另外临时表真的是一个好东西啊，让代码清晰化

with t as (
    select
        id,
        job,
        score,
        row_number() over(partition by job order by score desc) as t_rank,
        count(job) over(partition by job) as job_num
    from grade
)


select
    id,
    job,
    score,
    t_rank
from t
where
    case
        when job_num % 2 = 0 then t_rank = job_num / 2 or t_rank = job_num / 2 + 1
        else t_rank = ceil(job_num / 2)
    end
order by id