首页 > 试题广场 >

统计各个部门平均薪资

[编程题]统计各个部门平均薪资
  • 热度指数:17283 时间限制:C/C++ 1秒,其他语言2秒 空间限制:C/C++ 256M,其他语言512M
  • 算法知识视频讲解
某公司员工信息数据及员工薪资信息数据如下:
员工信息表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
select department,
round(avg(normal_salary-dock_salary),3) avg_salary
from salary_tb
join staff_tb
using(staff_id)
where (normal_salary-dock_salary)>=4000 and normal_salary-dock_salary<=30000
group by department
order by avg_salary desc;
发表于 2025-10-04 10:33:16 回复(0)
select
    department,
    round(
        sum(
            if(
                4000 < normal_salary - dock_salary
                and normal_salary - dock_salary < 30000,
                normal_salary - dock_salary,
                0
            )
        ) / sum(
            if(
                4000 < normal_salary - dock_salary
                and normal_salary - dock_salary < 30000,
                1,
                0
            )
        ),
        3
    ) as avg_salary
from
    staff_tb as s
    join salary_tb as st using (staff_id)
group by
    1
order by
    2 desc
发表于 2025-09-20 10:34:14 回复(1)
这题“薪资小于4000和大于30000的员工 ”实际上是不小于和不大于,不然报错
发表于 2025-02-19 17:13:01 回复(0)
select distinct
    department,
    avg(normal_salary - dock_salary) over (
        partition by
            department
    ) avg_salary
from
    staff_tb
    join salary_tb using (staff_id)
where
    (normal_salary - dock_salary) >= 4000
    and (normal_salary - dock_salary) <= 30000
order by
    2 desc

发表于 2025-02-10 09:37:43 回复(0)