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

# 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