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

SELECT  DISTINCT job,IF(up_rk<=down_rk,up_rk,down_rk) AS START
        ,IF(up_rk<=down_rk,down_rk,up_rk) AS END
FROM
(
SELECT *
      ,DENSE_RANK() OVER(PARTITION BY job ORDER BY score) AS up_rk
      ,DENSE_RANK() OVER(PARTITION BY job ORDER BY score DESC) AS down_rk
      ,COUNT(job) OVER( PARTITION BY job) AS total
FROM grade
) AS t1
WHERE up_rk>=total/2 AND down_rk>=total/2