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

select department,
round(ifnull(sum(normal_salary - dock_salary) / count(*) ,0) , 2)as average_actual_salary,
round(ifnull(sum(case when staff_gender = 'male' then normal_salary - dock_salary else 0 end) /
count(case when staff_gender = 'male' then staff_id else null end) ,0),2) as average_actual_salary_male,
round(ifnull(sum(case when staff_gender = 'female' then normal_salary - dock_salary else 0 end) /
count(case when staff_gender = 'female' then staff_id else null end) ,0),2) as average_actual_salary_female
from staff_tb
left join salary_tb using(staff_id)
group by department
order by average_actual_salary desc