题解 | #考试分数(四)#
考试分数(四)
http://www.nowcoder.com/practice/502fb6e2b1ad4e56aa2e0dd90c6edf3c
用户数为奇数时，中位数只有一个，因此start和end都为floor((count(*)+1)/2)
用户数为偶数时，中位数有两个，分别为floor(count(*)/2) 和 floor((count(*)+2)/2)
因为对于奇数来说，floor((count(*)+1)/2) 和 floor((count(*)+2)/2)结果一样
对于偶数来说，floor(count(*)/2) 和 floor(count(*)+1/2)结果一样
因此可以整合为一种情况，
即奇数和偶数的start都为 floor((count(*)+1)/2)，
end都为floor((count(*)+2)/2)
SELECT job, FLOOR((COUNT(*)+1)/2) AS start, FLOOR((COUNT(*)+2)/2) AS end FROM grade GROUP BY job ORDER BY job
下面是第一次写的代码，有点蠢，没想清楚该怎么合并
SELECT t.job AS job, ROUND(num/2,0) AS start, (CASE WHEN t.num % 2 = 1 THEN ROUND(t.num/2,0) WHEN t.num % 2 = 0 THEN ROUND(t.num/2,0) + 1 END) AS end FROM ( SELECT job, COUNT(*) AS num FROM grade GROUP BY job ) AS t ORDER BY t.job
查看20道真题和解析