在where里面模拟casewhen| #考试分数(五)#

with ranked as (select id, job, score, row_number() over (partition by job order by score DESC) as t_rank, count(id) over (partition by job) as cnt from grade)

select id, job, score, t_rank from ranked
where
    (cnt <= 2 and t_rank <= 2) or
    (cnt > 2 and cnt % 2 = 0 and (t_rank = cnt/2 or t_rank = cnt/2 +1)) or
    (cnt > 2 and cnt % 2 = 1 and t_rank = ceiling(cnt/2))

order by id