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

1. mysql分步操作

SELECT a.job, 
CASE (MIN(a.number) + MAX(a.number))%2
    WHEN 0 THEN
        ROUND((MIN(a.number) + MAX(a.number))/2,0)
    ELSE
        FLOOR((MIN(a.number) + MAX(a.number))/2)
END start,
CASE (MIN(a.number) + MAX(a.number))%2
    WHEN 0 THEN
        ROUND((MIN(a.number) + MAX(a.number))/2,0)
    ELSE
        CEIL((MIN(a.number) + MAX(a.number))/2)
END end
FROM
(
SELECT 
id,job,score,
RANK() OVER(PARTITION BY job ORDER BY score,job) AS number
FROM grade
)a
GROUP BY
a.job