569. Median Employee Salary----通过分组排序来判断中位数

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分类排名问题