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

# 方法二:
# 按最小颗粒度进行细分聚合之后,再按部门进行聚合模糊了性别
# 按性别加权平均反推出员工综合平均薪资
select department,
round(sum(total)/sum(nums),2) average_actual_salary,
round(sum(average_salary_male),2) average_actual_salary_male,
round(sum(average_salary_female),2)average_actual_salary_female
from(
    select department,
staff_gender,
sum(normal_salary-dock_salary) total,
count(s.staff_id) nums,
if(staff_gender = 'male',sum(normal_salary-dock_salary)/count(s.staff_id),0.00) average_salary_male,
if(staff_gender = 'female',sum(normal_salary-dock_salary)/count(s.staff_id),0.00) average_salary_female
from staff_tb s
left join salary_tb a on s.staff_id = a.staff_id
group by department,staff_gender
) t1
group by department
order by average_actual_salary desc