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

WITH T AS
(
    select job
            , sum(1) max_1
    from grade
    group by job
    order by job
)
SELECT 
    G.id
    ,G.job
    ,G.score
    ,G.t_rank 
FROM (SELECT 
    *
    ,ROW_NUMBER() OVER (PARTITION BY job ORDER BY score DESC) t_rank 
FROM grade) G
LEFT JOIN T ON G.job = T.job
WHERE G.t_rank IN (floor((T.max_1+1)/2),floor((T.max_1+2)/2))
ORDER BY G.id
;

# WITH T AS
# (
#     select job
#             ,floor((sum(1)+1)/2) as start
#             ,floor((sum(1)+2)/2) as end
#             , sum(1) max_1
#     from grade
#     group by job
#     order by job
# )
# SELECT *
# FROM T
# ;