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

select
        department,
        round(sum(normal_salary - dock_salary) / count(department),2) as average_actual_salary,
        ifnull(round(sum(if(staff_gender = 'male',normal_salary,Null) - if(staff_gender = 'male',dock_salary,Null)) / count(if(staff_gender = 'male',1,null)),2),0.00) as average_actual_salary_male,
        ifnull(round(sum(if(staff_gender = 'female',normal_salary,Null) - if(staff_gender = 'female',dock_salary,Null)) / count(if(staff_gender = 'female',1,null)),2),0.00) as average_actual_salary_female
from
        staff_tb as st
left join 
        salary_tb as slt
on 
        st.staff_id = slt.staff_id
group by 
        department
order by 
        average_actual_salary desc