SQL - 7

10.员工薪水中位数

需求:请编写SQL查询来查找每个公司的薪水中位数

结果展示:

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

建表语句:

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', 2865);
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);

最终SQL:

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)

作者:yuexiuping
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利.
posted @ 2021-06-21 22:23  yuexiuping  阅读(13)  评论(0编辑  收藏  举报