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

select
    distinct
        t1.department,
        t1.average_actual_salary,
        ifnull(t2.average_actual_salary_male,0.00) as average_actual_salary_male,
        ifnull(t2.average_actual_salary_female,0.00) as average_actual_salary_female
from
    (select
        st.department,
        round(avg(at.normal_salary - at.dock_salary),2) as average_actual_salary
    from
        staff_tb st
    left join
        salary_tb at on st.staff_id = at.staff_id
    group by 
        st.department) t1
left join
    (select
        st.department,
        round(avg(if(st.staff_gender = 'male',at.normal_salary - at.dock_salary,null)),2) as average_actual_salary_male,
        round(avg(if(st.staff_gender = 'female',at.normal_salary - at.dock_salary,null)),2) as average_actual_salary_female
    from
        staff_tb st
    left join
        salary_tb at on st.staff_id = at.staff_id
    group by 
        st.department) t2 on t1.department = t2.department
order by 
    t1.average_actual_salary desc