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 |
+-----+------------+--------+
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;
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 向下取整数