本文主要是介绍SQL - 7，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

10.员工薪水中位数

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

结果展示：

建表语句：

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)

这篇关于SQL - 7的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！