某公司员工信息数据及员工薪资信息数据如下： 员工信息表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=4500030000(剔除) 故结果为(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
加载中...