题解 | 各个部门实际平均薪资和男女员工实际平均薪资

各个部门实际平均薪资和男女员工实际平均薪资

https://www.nowcoder.com/practice/e8272685d07347cc88667f31f7989231

# with t1 as(
#   select
# department,
# staff_gender,
# round(sum(normal_salary-dock_salary),2) salary,
# count(a.staff_id) staffs,
# round(sum(normal_salary-dock_salary)/count(a.staff_id),2) average_actual_salary
# from
# salary_tb a inner join staff_tb b on a.staff_id=b.staff_id
# group by department,staff_gender  
# ),
# t2 as(
#   select
# department,
# sum(if(staff_gender='female',salary,0)) salary_female,
# sum(if(staff_gender='male',salary,0)) salary_male,
# sum(if(staff_gender='female',staffs,0)) staffs_female,
# sum(if(staff_gender='male',staffs,0)) staffs_male,
# sum(if(staff_gender='female',average_actual_salary,0)) average_actual_salary_female,
# sum(if(staff_gender='male',average_actual_salary,0)) average_actual_salary_male
# from
# t1
# group by department  
# )

# select 
# department,
# round((salary_female+salary_male)/(staffs_female+staffs_male),2) average_actual_salary,average_actual_salary_male,
# average_actual_salary_female

# from
# t2
# order by average_actual_salary desc

select
department,
ifNull(round(avg(normal_salary-dock_salary),2),0.00) average_actual_salary,
ifNull(round(avg(if(staff_gender='male',normal_salary-dock_salary,null)),2),0.00)average_actual_salary_male,
round(avg(if(staff_gender='female',normal_salary-dock_salary,null)),2) average_actual_salary_female
from
salary_tb a inner join staff_tb b on a.staff_id=b.staff_id
group by department
order by average_actual_salary desc


































全部评论

相关推荐

迟缓的斜杠青年巴比Q了:简历被投过的公司卖出去了,我前两天遇到过更离谱的,打电话来问我有没有意向报班学Java学习,服了,还拿我学校一个学长在他们那报班学了之后干了华为OD当招牌
点赞 评论 收藏
分享
04-03 12:09
東京大学 C++
求求求求暑期offer:留第一行,剩下的不要
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客企业服务