569. 员工薪水中位数

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     |
+-----+------------+--------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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

+-----+------------+--------+
|Id   | Company    | Salary |
+-----+------------+--------+
|5    | A          | 451    |
|6    | A          | 513    |
|12   | B          | 234    |
|9    | B          | 1154   |
|14   | C          | 2645   |
+-----+------------+--------+
1
2
3
4
5
6
7
8
9

drop table if EXISTS Employee;
CREATE TABLE If Not Exists Employee (Id INT, Company varchar(50), Salary int);

insert into Employee values (1,'A','2341');
insert into Employee values (2,'A','341');
insert into Employee values (3,'A','15');
insert into Employee values (4,'A','15314');
insert into Employee values (5,'A','451');
insert into Employee values (6,'A','513');
insert into Employee values (7,'B','15');
insert into Employee values (8,'B','13');
insert into Employee values (9,'B','1154');
insert into Employee values (10,'B','1345');
insert into Employee values (11,'B','1221');
insert into Employee values (12,'B','234');
insert into Employee values (13,'C','2345');
insert into Employee values (14,'C','2645');
insert into Employee values (15,'C','2645');
insert into Employee values (16,'C','2652');
insert into Employee values (17,'C','65');
select * from Employee;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

select Id,Company,Salary
from (
select Id,Company,Salary,
ROW_NUMBER() over(partition by Company order by Salary) rk,
count(*) over(partition by Company) cnt
from Employee
)t1
where rk IN (FLOOR((cnt + 1)/2), FLOOR((cnt + 2)/2))

//order by是由顺序要求的,自己的解法
select Id,Company,Salary
from(
select Id,Company,Salary,count(*) over(partition by company) rk_count,row_number() over(partition by company order by salary) rk
from employee
)tmp1
where rk between ceil(rk_count/2) and floor(rk_count/2)+1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

中位数：

+1向上取整 +2 向下取整数