首页 > 试题广场 >

统计各个部门平均薪资

[编程题]统计各个部门平均薪资
  • 热度指数：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 staff_tb st join salary_tb sa using(staff_id)
where normal_salary-dock_salary between 4000 and 30000
group by department
order by 2 desc
发表于 2025-02-26 16:38:44 回复(0)
海边的微风头像 海边的微风 
select department, round(avg(normal_salary-dock_salary),3) as avg_salary
from salary_tb
inner 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-02-12 17:38:20 回复(0)
烦恼的伊泽瑞尔不讲武德头像 烦恼的伊泽瑞尔不讲武德
select a.department as 'department',
        round(sum(b.normal_salary-b.dock_salary)/count(*),3) as'avg_salary'
from staff_tb a left join salary_tb b on a.staff_id=b.staff_id where b.normal_salary-b.dock_salary>=4000 and b.normal_salary-b.dock_salary<=30000
group by department order by round(sum(b.normal_salary-b.dock_salary)/count(*),3) desc
发表于 2023-09-06 23:43:26 回复(0)
婉秋头像 婉秋 
select department,
    round(avg(normal_salary-dock_salary),3) as avg_salary
from salary_tb s 
left join staff_tb sf using (staff_id)
where normal_salary-dock_salary between 4000 and 30000
group by department
order by avg_salary desc

发表于 2025-10-18 21:52:16 回复(0)
像初雪一样遇见你头像 像初雪一样遇见你 
select
department
,round(avg(normal_salary-dock_salary),3) as avg_salary
from staff_tb
join salary_tb
using(staff_id)
where (normal_salary-dock_salary) between 4000 and 30000
group by 1
order by 2 desc

发表于 2025-10-16 14:43:35 回复(0)
Mysql
一飞冲天的包子头像 一飞冲天的包子
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(avg(normal_salary-dock_salary),3) avg_salary
from staff_tb s,salary_tb st
where st.staff_id=s.staff_id and (normal_salary-dock_salary)>=4000 and (normal_salary-dock_salary)<=30000
group by department
order by avg_salary desc
发表于 2025-09-24 09:24:25 回复(0)
镭射鱼头像 镭射鱼
with
temp as (
select
a.staff_id,
department,
normal_salary - dock_salary as salary
from
staff_tb a
left join salary_tb b on a.staff_id = b.staff_id
where
normal_salary - dock_salary >= 4000
AND normal_salary - dock_salary <= 30000
)
select
department,
round(avg(salary), 3) as avg_salary
from
temp
group by
department
order by
avg_salary desc;
发表于 2025-09-23 01:28:59 回复(0)
Mysql
在做ppt的孤勇者很讲道理头像 在做ppt的孤勇者很讲道理
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)
牛客689009255号头像 牛客689009255号
发现一个问题，题目里面表示：统计平均薪资要求剔除薪资小于4000和大于30000的员工。按理来说salary应该是between 4001 and 29999（或者写salary > 4000 and salary < 30000）。但是在第二轮测试的时候，有一个人的salary=30000，这么写结果会报错，因为没有计算那个salary=30000的数据。
最后sql改为salary >= 4000 and salary <= 30000，就通过了。
发表于 2025-08-19 16:31:28 回复(0)
爱吃鱼的大学生有点心碎头像 爱吃鱼的大学生有点心碎
SELECT
    ST.department AS department,
    ROUND(SUM(SA.salary)/COUNT(*),3) AS avg_salary
FROM staff_tb AS ST
INNER JOIN (SELECT
                staff_id ,
                (A.normal_salary - A.dock_salary) AS salary
           FROM salary_tb AS A
           HAVING salary>= 4000 AND salary<= 30000  ) AS SA
ON ST.staff_id = SA.staff_id
GROUP BY ST.department
ORDER BY 2 DESC
发表于 2025-08-11 21:31:21 回复(0)
岩1_头像 岩1_
select
department,
avg(true_salary) as avg_salary
from
(
    select
    salary_id,
    staff_id,
    normal_salary - dock_salary as true_salary
    from
    salary_tb sy
)t

join
staff_tb sf
on
t.staff_id = sf.staff_id

where
true_salary between 4000 and 30000
group by
department
order by
avg_salary
desc
发表于 2025-08-07 10:46:26 回复(0)
小猫爱学习头像 小猫爱学习
select department, round(avg(normal_salary-dock_salary),3) avg_salary
from staff_tb a
join salary_tb b
on a.staff_id = b.staff_id
where normal_salary-dock_salary > 4000 and normal_salary-dock_salary < 30000
group by department
order by avg_salary desc

这个提交为啥不对，自测是对的？
发表于 2025-08-03 18:48:47 回复(0)
抱着太阳看月亮头像 抱着太阳看月亮
with a as (
    select department,round(avg(normal_salary-dock_salary),3) as avg_salary
    from staff_tb b
    left join salary_tb c on b.staff_id = c.staff_id
    where normal_salary-dock_salary between 4000 and 30000
    group by department
    order by avg_salary desc
)
select * from a
发表于 2025-07-29 13:47:38 回复(0)
牛牛688头像 牛牛688
这道题好奇怪，为什么单独运行这个，出来的结果和实例不匹配？
 select
            st.staff_id,
            st.department,
            sa.normal_salary - sa.dock_salary reals
        from
            staff_tb st
        left join salary_tb sa
        on st.staff_id = sa.staff_id

发表于 2025-07-17 17:36:26 回复(0)
你是最棒的zy头像 你是最棒的zy 
select department,round(sum(normal_salary-dock_salary)/count(salary_id),3) as avg_salary
from staff_tb st 
left join salary_tb sat on sat.staff_id=st.staff_id
where normal_salary-dock_salary between 4000 and 30000
group by department
order by avg_salary desc

发表于 2025-07-14 16:47:49 回复(0)
cilantro085头像 cilantro085 
select st.department, sum(sa.normal_salary-sa.dock_salary)/count(*) as avg_salary
from staff_tb st
join salary_tb sa on st.staff_id = sa.staff_id
where sa.normal_salary-sa.dock_salary between 4000 and 30000
group by st.department
order by avg_salary desc

发表于 2025-07-11 21:57:45 回复(0)
破烂儿_头像 破烂儿_
    department,
    ROUND(AVG(normal_salary - dock_salary),3) AS avg_salary
FROM staff_tb st
JOIN salary_tb sa USING (staff_id)
WHERE (normal_salary - dock_salary) BETWEEN 4000 AND 30000
GROUP BY department
ORDER BY avg_salary desc
发表于 2025-07-11 10:48:40 回复(0)
西柚汁源源头像 西柚汁源源
select department,sum(normal_salary-dock_salary)/count(*) avg_salary
from staff_tb st join salary_tb sa on st.staff_id=sa.staff_id
where normal_salary-dock_salary between 4000 and 30000#不能用having，就会变成条件筛除部门
group by department
order by avg_salary desc
发表于 2025-06-24 09:56:40 回复(0)
Deep呼吸头像 Deep呼吸 
select
    department,
    round(avg(normal_salary-dock_salary),3) as avg_salary 
from staff_tb st
join salary_tb sa 
on st.staff_id = sa.staff_id 
where normal_salary-dock_salary >=4000
    and normal_salary-dock_salary <=30000
group by department
order by avg_salary desc
希望高人挑剔一下
发表于 2025-06-10 15:46:14 回复(2)
提交观点

问题信息

SQL
来自：2023年携程秋招技术...
难度：
65条回答 1035浏览

热门推荐

相关试题

统计各个部门平均薪资

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号