The Employee
table holds all employees. The employee table has three columns: Employee Id, Company Name, and Salary.
+-----+------------+--------+
|Id | Company | Salary |
+-----+------------+--------+
|1 | A | 2341 |
|2 | A | 341 |
|3 | A | 15 |
|4 | A | 15314 |
|5 | A | 451 |
|6 | A | 513 |
|7 | B | 15 |
|8 | B | 13 |
|9 | B | 1154 |
|10 | B | 1345 |
|11 | B | 1221 |
|12 | B | 234 |
|13 | C | 2345 |
|14 | C | 2645 |
|15 | C | 2645 |
|16 | C | 2652 |
|17 | C | 65 |
+-----+------------+--------+
Write a SQL query to find the median salary of each company. Bonus points if you can solve it without using any built-in SQL functions.
+-----+------------+--------+
|Id | Company | Salary |
+-----+------------+--------+
|5 | A | 451 |
|6 | A | 513 |
|12 | B | 234 |
|9 | B | 1154 |
|14 | C | 2645 |
+-----+------------+--------+
解题思路:通过窗口函数对每个公司的职员进行编号,并统计职员数量。如果数量couting是偶数,则ranking = (counting/2,counting/2 +1),如果是奇数,ranking = (couting+1)/2,但是counting/2,counting/2 +1,(couting+1)/2 不会同时是整数,所以直接可以可以筛选出中位数。
WITH tmp AS
(SELECT *,
COUNT(id) OVER (PARTITION BY company) AS counting,
ROW_NUMBER() OVER (PARTITION BY company ORDER BY salary) AS ranking
FROM employee)
SELECT id, company, salary
FROM tmp
WHERE ranking IN (counting/2, (counting+1)/2, counting/2+1);
其他解题思路见:愁!MySQL中如何查询中位数
分类排名示例:一文解决所有MySQL分类排名问题