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

select department,
round(avg(normal_salary-dock_salary),2) as average_actual_salary,

round(if(sum(if(staff_gender='male',1,0) )!=0,sum(if(staff_gender='male',normal_salary-dock_salary,0))/sum(if(staff_gender='male',1,0) ),0) ,2)as average_actual_salary_male,

round(if(sum(if(staff_gender='female',1,0) )!=0,sum(if(staff_gender='female',normal_salary-dock_salary,0))/sum(if(staff_gender='female',1,0) ),0) ,2) as average_actual_salary_female
from staff_tb
join salary_tb
using(staff_id)
group by department
order by average_actual_salary desc

我就这样一直if if if 麻了