Leetcode力扣 MySQL数据库 569.员工薪水中位数

569.员工薪水中位数

SQL架构

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

Employee 表包含所有员工。Employee_569 表有三列：员工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   |
+-----+------------+--------+
​*/
解题

select Id, Company, Salary
from
    (select *,
            row_number() over (partition by Company order by Salary) rnk,
            count(*) over (partition by Company) num
    from Employee_569) t
where rnk in (floor((num + 1) / 2), floor(num + 2) / 2);