首页 > 试题广场 >

统计各个部门平均薪资

[编程题]统计各个部门平均薪资
  • 热度指数：26752 时间限制：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)
Sqlite
BraveCoder头像 BraveCoder 
select
    department,
    round(avg(normal_salary - dock_salary), 3) as avg_salary
from
    salary_tb as a
    inner join staff_tb as 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-11-07 21:02:33 回复(0)
牛客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 回复(2)
海边的微风头像 海边的微风 
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)
牛客178163380号头像 牛客178163380号 
with t1 as (
    select 
        st.staff_id,
        st.department,
        (sal.normal_salary - sal.dock_salary) as true_sal
    from staff_tb st
    inner join salary_tb sal using(staff_id)
)
, t2 as (
    select 
        department,
        true_sal
    from t1
    where true_sal >= 4000 and true_sal <= 30000
)
, t3 as (
    select 
        department, 
        avg(true_sal) as avg_salary
    from t2 
    group by department
)

select 
    *
from t3
order by avg_salary desc 

发表于 2026-03-01 06:49:35 回复(0)
找实习的牛客头像 找实习的牛客 
select
department,
avg(salary) avg_salary
from (
select
staff_id,
normal_salary-dock_salary salary
from salary_tb where normal_salary-dock_salary>=4000 and normal_salary-dock_salary<=30000)
t1
left join staff_tb
on t1.staff_id=staff_tb.staff_id
group by department
order by avg_salary desc;

发表于 2026-02-12 10:30:30 回复(0)
不知道哦_头像 不知道哦_ 
select
    a.department,
    round(avg(b.normal_salary - b.dock_salary), 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) between 4000 and 30000
group by
    a.department
order by
    avg_salary desc;

发表于 2026-01-24 12:32:31 回复(0)
虫哇头像 虫哇
with t1 as (
    select staff_name, sta.department,
    (s.normal_salary - s.dock_salary) as salary
    from staff_tb sta
    left join salary_tb s
    on sta.staff_id = s.staff_id
    where (s.normal_salary - s.dock_salary) between 4000 and 30000
)

select distinct department,
avg(salary) over(partition by department) as avg_salary
from t1
order by avg_salary desc

发表于 2026-01-23 15:28:07 回复(0)
牛客192267230号头像 牛客192267230号
select department,round(avg(normal_salary-dock_salary),3) as avg_salary
from salary_tb t1
left join staff_tb t2 on t1.staff_id=t2.staff_id
where (normal_salary-dock_salary) between '4000' and '30000'
group by department
order by avg_salary desc
发表于 2026-01-15 18:46:05 回复(0)
jojozyh头像 jojozyh 
# 注意3位小数，薪资降序，实际薪资在[4000, 30000]
SELECT department, ROUND(AVG(normal_salary - dock_salary), 3) AS avg_salary
FROM salary_tb JOIN staff_tb USING (staff_id)
WHERE normal_salary - dock_salary BETWEEN 4000 AND 30000
GROUP BY 1
ORDER BY 2 DESC

发表于 2026-01-09 11:41:44 回复(0)
可爱的王者天天摸鱼头像 可爱的王者天天摸鱼
select  department,avg(salary) avg_salary from (
select st.staff_id,(normal_salary-dock_salary) as salary ,department
from  salary_tb st join staff_tb sb on st.staff_id =sb.staff_id)t1 where
salary between 4000 and 30000
group by department order by avg_salary desc
发表于 2026-01-05 17:58:04 回复(0)
有胆量的托尼在迎接offer头像 有胆量的托尼在迎接offer
Select f.department,
    round(sum(s.normal_salary-s.dock_salary)/count(*),3) as avg_salary
from staff_tb f
join salary_tb s on f.staff_id = s.staff_id
where s.normal_salary-s.dock_salary >= 4000 and s.normal_salary-s.dock_salary <= 30000
group by f.department
order by avg_salary desc
发表于 2025-12-13 22:37:59 回复(0)
Sqlite
牛客396459651号头像 牛客396459651号
select t2.department,
    round(avg(t1.actual_salary)) avg_salary
from (select *,
        (normal_salary - dock_salary) actual_salary
    from salary_tb
    having actual_salary between 4000 and 30000
) t1
join staff_tb t2
on t1.staff_id=t2.staff_id
group by t2.department
order by avg_salary desc

发表于 2025-12-09 19:25:41 回复(0)
hhhhuu头像 hhhhuu
select
    t1.department as department,
    round(avg(t2.normal_salary-t2.dock_salary),3) as avg_salary
from staff_tb t1
left join salary_tb t2 on t1.staff_id=t2.staff_id
where t2.normal_salary-t2.dock_salary between 4000 and 30000
group by t1.department
order by 2 desc
发表于 2025-12-08 19:41:25 回复(0)
牛客573393823号头像 牛客573393823号
select staff_tb.department, (round(sum(salary_tb.normal_salary-salary_tb.dock_salary)/count(staff_tb.staff_id),3)) as avg_salary
         from staff_tb join salary_tb on staff_tb.staff_id=salary_tb.staff_id
         where (salary_tb.normal_salary - salary_tb.dock_salary) between 4000 and 30000
         group by staff_tb.department
         order by avg_salary desc;
发表于 2025-11-14 16:41:17 回复(0)
无敌的王哥头像 无敌的王哥
select
department
,round(avg(if(normal_salary-dock_salary between 4000 and 30000,normal_salary-dock_salary,null)),3) avg_salary
from staff_tb st inner join salary_tb sa
on st.staff_id = sa.staff_id
group by department
order by avg_salary desc

发表于 2025-11-13 19:02:49 回复(1)
婉秋头像 婉秋 
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)
提交观点

问题信息

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

热门推荐

相关试题

统计各个部门平均薪资

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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