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

第一步计算每个岗位的中位数位置，用floor函数，即
SELECT   
            job,  
            FLOOR((COUNT(job) + 1) / 2) AS start,  
            FLOOR((COUNT(job) + 2) / 2) AS end 
        FROM   
            grade  
        GROUP BY   
            job 
第二步，运用窗口函数按照分数降序给岗位排序，即
 RANK() OVER (PARTITION BY g.job ORDER BY g.score desc) AS t_rank,
 第三步，将以上两步的结果用INNER JOIN链接形成一个新表，再select出需要显示的字段，并用WHERE sr.t_rank = sr.start OR sr.t_rank = sr.end进行筛选，最后对于结果进行id排序，即
 SELECT sr.id, sr.job, sr.score, sr.t_rank  
FROM (  
    SELECT   
        g.id,        
        g.job,  
        g.score,  
        RANK() OVER (PARTITION BY g.job ORDER BY g.score desc) AS t_rank,  
        r.start,  
        r.end  
    FROM   
        grade g  
    INNER JOIN (  
        SELECT   
            job,  
            FLOOR((COUNT(job) + 1) / 2) AS start,  
            FLOOR((COUNT(job) + 2) / 2) AS end  
        FROM   
            grade  
        GROUP BY   
            job  
    ) AS r ON g.job = r.job  
) AS sr  
WHERE sr.t_rank = sr.start OR sr.t_rank = sr.end
order by sr.id