题解 | #考试分数(四)#
考试分数(四)
https://www.nowcoder.com/practice/502fb6e2b1ad4e56aa2e0dd90c6edf3c
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
查看11道真题和解析
