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

with a as (select s.department,
round(sum(a.normal_salary-a.dock_salary)/count(s.staff_id),2) average_actual_salary
from staff_tb s left join salary_tb a
on s.staff_id = a.staff_id
group by s.department
), b as (select s.department,
s.staff_gender,
round(sum(a.normal_salary-a.dock_salary)/count(s.staff_id),2) salary_gender,
if(staff_gender = 'male',round(sum(a.normal_salary-a.dock_salary)/count(s.staff_id),2),0.00) average_salary_male,
if(staff_gender = 'female',round(sum(a.normal_salary-a.dock_salary)/count(s.staff_id),2),0.00) average_salary_female
from staff_tb s left join salary_tb a
on s.staff_id = a.staff_id
group by s.department,s.staff_gender)

# 步骤一:
# salary_gender一列拆两列
# 使用if判断如果gender匹配则赋原值,不匹配则赋值为0
# department|staff_gender|salary_gender|average_salary_male|average_salary_female
# dep1      |male        |10133.33     |10133.33|0.00
# dep1      |female      |11200.00     |0.00    |11200.00
# dep2      |female      |31400.00     |0.00    |31400.00
# dep2      |male        |13500.00     |13500.00|0.00
# 步骤二:
# 根据department分组,对average_salary_male求和,对average_salary_female求和,拆分完成
# department|average_actual_salary_male|average_actual_salary_female
# dep2      |13500.00                  |31400.00
# dep1      |10133.33                  |11200.00

select a.department,
a.average_actual_salary,
sum(if(staff_gender = 'male',salary_gender,0.00)) average_actual_salary_male,
sum(if(staff_gender = 'female',salary_gender,0.00)) average_actual_salary_female
from a 
left join b on a.department = b.department
group by a.department
order by average_actual_salary desc