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

select department,
ifnull(round(avg(normal_salary - dock_salary),2),0.00) average_actual_salary,
ifnull(round(sum(if(staff_gender = 'male',normal_salary - dock_salary,0))/sum(if(staff_gender = 'male',1,0)),2),0.00) average_actual_salary_male,
ifnull(round(sum(if(staff_gender = 'female',normal_salary - dock_salary,0))/sum(if(staff_gender = 'female',1,0)),2),0.00) average_actual_salary_female
from staff_tb s1 join salary_tb s2 on s1.staff_id = s2.staff_id 
group by department
order by average_actual_salary desc