SQL案例 - 统计各个部门平均薪资

描述

某公司员工信息数据及员工薪资信息数据如下：

员工信息表staff_tb(staff_id-员工id，staff_name-员工姓名，staff_gender-员工性别，post-员工岗位类别，department-员工所在部门)，如下所示：

staff_id	staff_name	staff_gender	post	department
1	Angus	male	Financial	dep1
2	Cathy	female	Director	dep1
3	Aldis	female	Director	dep2
4	Lawson	male	Engineer	dep1
5	Carl	male	Engineer	dep2
6	Ben	male	Engineer	dep1
7	Rose	female	Financial	dep2

员工薪资信息表salary_tb(salary_id-薪资信息id，taff_id-员工id，normal_salary-标准薪资，dock_salary-扣除薪资)，如下所示：

salary_id	staff_id	normal_salary	dock_salary
10	1	12000	2500
11	2	11000	2200
12	3	9000	1800
13	4	10500	1900
14	5	13500	2100
15	6	7500	1000
16	7	50000	5000

问题：请统计各个部门平均实发薪资？

注：实发薪资=标准薪资-扣除薪资，统计平均薪资要求剔除薪资小于4000和大于30000的员工

要求输出：部门，平均实发薪资（保留3位小数）按照平均实发薪资降序排序
示例数据结果如下：

department	avg_salary
dep2	9300.000
dep1	8350.000

解释：部门dep2共有员工3、5、7

实发薪资分别为9000-1800=7200、13500-2100=11400、50000-5000=45000>30000(剔除)

故结果为(7200+11400)/2=9300.000;

其他结果同理。

示例1

输入：

drop table if exists  `staff_tb` ; 
CREATE TABLE `staff_tb` (
`staff_id` int(11) NOT NULL,
`staff_name` varchar(16) NOT NULL,
`staff_gender` char(8) NOT NULL,
`post` varchar(11) NOT NULL,
`department` varchar(16) NOT NULL,
PRIMARY KEY (`staff_id`));
INSERT INTO staff_tb VALUES(1,'Angus','male','Financial','dep1'); 
INSERT INTO staff_tb VALUES(2,'Cathy','female','Director','dep1'); 
INSERT INTO staff_tb VALUES(3,'Aldis','female','Director','dep2'); 
INSERT INTO staff_tb VALUES(4,'Lawson','male','Engineer','dep1'); 
INSERT INTO staff_tb VALUES(5,'Carl','male','Engineer','dep2'); 
INSERT INTO staff_tb VALUES(6,'Ben','male','Engineer','dep1'); 
INSERT INTO staff_tb VALUES(7,'Rose','female','Financial','dep2'); 

drop table if exists  `salary_tb` ; 
CREATE TABLE `salary_tb` (
`salary_id` int(11) NOT NULL,
`staff_id` int(11) NOT NULL,
`normal_salary` int(11) NOT NULL,
`dock_salary` int(11) NOT NULL,
PRIMARY KEY (`salary_id`));
INSERT INTO salary_tb VALUES(10,1,12000,2500); 
INSERT INTO salary_tb VALUES(11,2,11000,2200); 
INSERT INTO salary_tb VALUES(12,3,9000,1800); 
INSERT INTO salary_tb VALUES(13,4,10500,1900); 
INSERT INTO salary_tb VALUES(14,5,13500,2100); 
INSERT INTO salary_tb VALUES(15,6,7500,1000); 
INSERT INTO salary_tb VALUES(16,7,50000,5000);

输出：

department|avg_salary
dep2|9300.000
dep1|8350.000

答案

解法1：

select
    e.department,
    round(avg(s.normal_salary-s.dock_salary),3) avg_salary
from
    staff_tb e
    left join salary_tb s on e.staff_id=s.staff_id
where
    s.normal_salary-s.dock_salary between 4000 and 30000
group by 
    e.department
order by avg_salary desc;

*本案例来自牛客网，但答案为原创，如有雷同纯属巧合*