题解 | #考试分数(四)#
考试分数(四)
http://www.nowcoder.com/practice/502fb6e2b1ad4e56aa2e0dd90c6edf3c
- 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 
