我要招人

去企业版

首页 > 试题广场 >

统计各个部门平均薪资

[编程题]统计各个部门平均薪资

热度指数：104 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

某公司员工信息数据及员工薪资信息数据如下：

员工信息表staff_tb(staff_id-员工id,staff_name-员工姓名,staff_gender-员工性别,post-员工岗位类别,department-员工所在部门)，如下所示：

员工薪资信息表salary_tb(salary_id-薪资信息id,staff_id-员工id,normal_salary-标准薪资,dock_salary-扣除薪资)，如下所示：

问题：请统计各个部门平均实发薪资？

注：实发薪资=标准薪资-扣除薪资，统计平均薪资要求剔除薪资小于4000和大于30000的员工

要求输出：部门，平均实发薪资（保留3位小数）按照平均实发薪资降序排序
示例数据结果如下：

解释：部门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 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)

牛客819444008号

select department, ROUND(avg(normal_salary - dock_salary),3) as avg_salary

from staff_tb st

INNER JOIN salary_tb sa ON st.staff_id = sa.staff_id

where (normal_salary - dock_salary) between 4000 and 30000

group by department

order by avg_salary DESC

发表于 2024-03-13 18:57:04 回复(0)

mJieg

select department, AVG(asa) as avg_salary from

(select staff_id, normal_salary nsa, dock_salary dsa, department,

normal_salary-dock_salary as asa

from salary_tb sa natural join staff_tb st

where normal_salary-dock_salary >= 4000and normal_salary-dock_salary <= 30000

) tb

group by department

order by avg_salary desc;

发表于 2023-10-27 00:12:57 回复(0)

陆地神仙

select department, all_salary / number as avg_salary from (
select department, sum(normal_salary - dock_salary) as all_salary, count(staff_tb.staff_id) as number
from staff_tb join salary_tb on staff_tb.staff_id = salary_tb.staff_id
where normal_salary - dock_salary >= 4000 and normal_salary - dock_salary <= 30000
group by staff_tb.department
) as temp_tb
order by avg_salary desc;

发表于 2023-09-04 20:23:44 回复(0)

提交观点

问题信息

来自：2022年携程秋招第一批

难度：

4条回答 4收藏 489浏览

统计各个部门平均薪资

输入

输出

问题信息

热门推荐

相关试题