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

法一（先窗口函数排序了，其实不需要的，因为只需要分组后的总数就可以了，捂脸）：

SELECT job, case when MAX(rk)%2=0 then ROUND(MAX(rk)/2) else ROUND(MAX(rk)/2) end AS start,
case when MAX(rk)%2=0 then ROUND(MAX(rk)/2)+1 else ROUND(MAX(rk)/2) end AS end
FROM (SELECT job, ROW_NUMBER() over (partition by job order by score) rk FROM grade) t1
GROUP BY job
ORDER BY job

法二（用count代替）：

SELECT job, case when num%2=0 then ROUND(num/2) else ROUND(num/2) end AS start,
case when num%2=0 then ROUND(num/2)+1 else ROUND(num/2) end AS end
FROM (SELECT job, COUNT(*) num FROM grade GROUP BY job) t1
GROUP BY job
ORDER BY job