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

select department,
average_actual_salary,
ifnull(average_actual_salary_male,0) as average_actual_salary_male,
ifnull(average_actual_salary_female,0) as average_actual_salary_female
from
    (select department,
    round(avg(normal_salary - dock_salary),2) as average_actual_salary
    from salary_tb
    join staff_tb
    using(staff_id)
    group by department) a
left join
    (select department,
    round(avg(normal_salary - dock_salary),2) as average_actual_salary_male
    from salary_tb
    join staff_tb
    using(staff_id)
    where staff_gender = 'male'
    group by department
    ) b
using(department)
left join
    (select department,
    round(avg(normal_salary - dock_salary),2) as average_actual_salary_female
    from salary_tb
    join staff_tb
    using(staff_id)
    where staff_gender = 'female'
    group by department) c
using(department)
order by average_actual_salary desc