569.(Hard)员工薪水中位数

Employee 表包含所有员工。Employee 表有三列：员工Id，公司名和薪水。

+-----+------------+--------+
|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     |
+-----+------------+--------+

请编写SQL查询来查找每个公司的薪水中位数。挑战点：你是否可以在不使用任何内置的SQL函数的情况下解决此问题。

+-----+------------+--------+
|Id   | Company    | Salary |
+-----+------------+--------+
|5    | A          | 451    |
|6    | A          | 513    |
|12   | B          | 234    |
|9    | B          | 1154   |
|14   | C          | 2645   |
+-----+------------+--------+

来源：力扣（LeetCode）

链接：https://leetcode-cn.com/problems/median-employee-salary

著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

Solution

窗口函数

SELECT DISTINCT Id, Company, Salary
FROM (
    SELECT *, 
        ROW_NUMBER() over(PARTITION by Company ORDER BY Salary) rk,
        COUNT(1) over(PARTITION by Company) n
    FROM Employee
) t 
WHERE rk in (n/2, n/2+1, n/2+0.5)

自连接

SELECT a.Id, a.Company, MAX(a.Salary) Salary
FROM Employee a INNER JOIN Employee b USING(Company)
GROUP BY 1, 2
HAVING ABS(SUM(case when a.Salary > b.Salary THEN 1
                    when a.Salary < b.Salary THEN -1  -- salary 大小相互抵消
                    WHEN a.Id > b.Id THEN 1
                    WHEN a.Id < b.id THEN -1          -- ID 大小相互抵消
                    ELSE 0 END))<=1

Table Schema

Create table If Not Exists Employee (Id int, Company varchar(255), Salary int);
Truncate table Employee;
insert into Employee (Id, Company, Salary) values ('1', 'A', '2341');
insert into Employee (Id, Company, Salary) values ('2', 'A', '341');
insert into Employee (Id, Company, Salary) values ('3', 'A', '15');
insert into Employee (Id, Company, Salary) values ('4', 'A', '15314');
insert into Employee (Id, Company, Salary) values ('5', 'A', '451');
insert into Employee (Id, Company, Salary) values ('6', 'A', '513');
insert into Employee (Id, Company, Salary) values ('7', 'B', '15');
insert into Employee (Id, Company, Salary) values ('8', 'B', '13');
insert into Employee (Id, Company, Salary) values ('9', 'B', '1154');
insert into Employee (Id, Company, Salary) values ('10', 'B', '1345');
insert into Employee (Id, Company, Salary) values ('11', 'B', '1221');
insert into Employee (Id, Company, Salary) values ('12', 'B', '234');
insert into Employee (Id, Company, Salary) values ('13', 'C', '2345');
insert into Employee (Id, Company, Salary) values ('14', 'C', '2645');
insert into Employee (Id, Company, Salary) values ('15', 'C', '2645');
insert into Employee (Id, Company, Salary) values ('16', 'C', '2652');
insert into Employee (Id, Company, Salary) values ('17', 'C', '65');