首页 > 试题广场 >

统计各个部门平均薪资

[编程题]统计各个部门平均薪资
  • 热度指数：8131 时间限制：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)
还没有工作的小张头像 还没有工作的小张 
WITH
  temp AS(
    SELECT 
        department,
        normal_salary - dock_salary AS real_salary
    FROM staff_tb
    JOIN salary_tb USING (staff_id)
    WHERE normal_salary - dock_salary BETWEEN 4000 AND 30000
  )

SELECT 
    department,
    ROUND(AVG(real_salary), 3) AS avg_salary
FROM temp
GROUP BY department
ORDER BY avg_salary DESC

发表于 2025-04-28 18:37:06 回复(0)
迟缓的小龙虾说我这儿没问题啊头像 迟缓的小龙虾说我这儿没问题啊
select
  department,
  round(avg(normal_salary-dock_salary),3) as avg_salary
from
  salary_tb sa
left join
  staff_tb st
on
  sa.staff_id=st.staff_id
where
  normal_salary-dock_salary between 4000 and 30000
group by
  department
order by
  avg_salary desc
发表于 2025-04-22 16:18:16 回复(0)
QingyunChen头像 QingyunChen 
select
    a.department,
    round(avg(b.normal_salary - b.dock_salary), 3) as avg_salary
from
    staff_tb a
    join salary_tb b on a.staff_id = b.staff_id
    and (b.normal_salary - b.dock_salary) >= 4000
    and (b.normal_salary - b.dock_salary) <= 30000
group by
    a.department
order by
    avg_salary desc

发表于 2025-04-18 11:57:46 回复(0)
小八飞毛腿头像 小八飞毛腿 
select department
    ,round(avg(normal_salary - dock_salary),3)  as avg_salary
from salary_tb a
left join staff_tb b 
on a.staff_id = b.staff_id
where  normal_salary-dock_salary>=4000 
    and normal_salary-dock_salary<=30000
group by 1
order by 2 desc

发表于 2025-04-17 23:36:44 回复(0)
kukuju头像 kukuju
select department,round(avg(normal_salary-dock_salary) ,3)as avg_salary
from staff_tb
left join salary_tb
using(staff_id)
where normal_salary-dock_salary between 4000 and  30000
group by department
order by avg_salary desc
发表于 2025-04-10 23:28:30 回复(0)
蜥蜴公主头像 蜥蜴公主
自测通过，保存并提交不通过，为什么啊？恳请大佬们指点
select
b.department
,round(avg(normal_salary-dock_salary),3) avg_salary
from salary_tb a
left join staff_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-04-10 15:29:52 回复(0)
牛客130469250号头像 牛客130469250号
select department,
ROUND(AVG(act_salary), 3) AS avg_salary
from
 (
 select *
 from  (select *,normal_salary - dock_salary as act_salary
 from salary_tb) t1
 where normal_salary - dock_salary>4000 and normal_salary - dock_salary<30000
 ) t2 join staff_tb st on st.staff_id = t2.staff_id
group by department
order by avg_salary desc

发表于 2025-04-02 11:56:42 回复(0)
曲径海盗头像 曲径海盗
select department,avg(tru_salary) avg_salary
from (
    select sb.staff_id,sb.department,(st.normal_salary - st.dock_salary) tru_salary
    from staff_tb sb
    inner join salary_tb st on sb.staff_id = st.staff_id
) sss
where tru_salary <= 30000 and tru_salary >= 4000
group by department
order by avg_salary desc

发表于 2025-03-25 22:08:36 回复(0)
牛客454170273号头像 牛客454170273号
select
    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
    department
order by
    avg_salary desc
发表于 2025-03-25 21:08:44 回复(0)
饥饿的牛油刷了100道题头像 饥饿的牛油刷了100道题 
select 
department,round(avg(normal_salary-dock_salary),3) avg_salary
from staff_tb s join salary_tb s2
on s.staff_id = s2.staff_id
where normal_salary-dock_salary between 4000 and 30000
group by department 
order by avg_salary desc

发表于 2025-03-25 00:09:15 回复(0)
全障碍学习头像 全障碍学习
with t1 as
(SELECT
staff_id,
sum(ROUND((normal_salary - dock_salary), 3)) AS salary
FROM
salary_tb
where (normal_salary-dock_salary) between '4000' and '30000'
GROUP BY
staff_id )
SELECT
t2.department,
avg(salary) as avg_salary
from t1
JOIN
staff_tb t2 ON t1.staff_id = t2.staff_id
GROUP BY
t2.department
ORDER BY
AVG(salary) DESC;

发表于 2025-03-24 12:48:45 回复(0)
在对齐目标的小鱼干很理智头像 在对齐目标的小鱼干很理智
select
b.department department,
round(avg(a.normal_salary-a.dock_salary),3) avg_salary
from salary_tb a left join staff_tb b
on a.staff_id=b.staff_id
where (a.normal_salary-a.dock_salary)>= 4000 and (a.normal_salary-a.dock_salary)<=30000
group by department
order by avg_salary desc
发表于 2025-03-18 13:10:05 回复(0)
牛客775456359号头像 牛客775456359号 
select department,
round(avg(normal_salary-dock_salary),3) avg_salary
from salary_tb
inner join staff_tb
on salary_tb.staff_id=staff_tb.staff_id
where (normal_salary-dock_salary)between 4000 and 30000
group by department
order by 2 desc;

发表于 2025-03-15 11:50:15 回复(0)
牛客201778390号头像 牛客201778390号
select department,round(avg(salary),3) as avg_salary
from
(select department,(normal_salary - dock_salary) as salary
 from staff_tb a
join  salary_tb sa
on  a.staff_id=sa.staff_id) b
where salary BETWEEN 4000 AND 30000
group by department
order by avg_salary desc

发表于 2025-03-13 19:48:46 回复(0)
前程似锦的沸羊羊很紧张头像 前程似锦的沸羊羊很紧张
select department,round(avg(st3.r_salary),3) avg_salary
from(
select st1.staff_id,st1.department,normal_salary-dock_salary r_salary
from staff_tb st1
join salary_tb st2
on st1.staff_id=st2.staff_id
where r_salary between 4000 and 30000) st3
group by department
order by avg_salary desc
有没有大神知道为什么where子句放在括号里边会报错呢
发表于 2025-03-06 18:43:37 回复(0)
牛客216927498号头像 牛客216927498号
select
department  
,avg(normal_salary-dock_salary) avg_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
group by 1
order by 2 desc
发表于 2025-03-06 16:49:13 回复(0)
牛客924005724号头像 牛客924005724号
select department
,round((sum(normal_salary-dock_salary)/count(s.staff_id)),3) avg_salary
from staff_tb s
left join salary_tb a on s.staff_id=a.staff_id
where normal_salary-dock_salary between 4000 and 30000
group by department
order by avg_salary desc;
发表于 2025-02-28 10:24:08 回复(0)
提交观点

问题信息

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

热门推荐

相关试题

统计各个部门平均薪资

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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