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

with
    a as (
        select
            staff_id,
            normal_salary - dock_salary nd
        from
            salary_tb
    ),
    t1 as (
        select
            department,
            round(avg(nd), 2) average_actual_salary
        from
            staff_tb st
            inner join a on st.staff_id = a.staff_id
        group by
            department
    ),
    t2 as (
        select
            department,
            ifnull(round(avg(nd), 2), 0.00) average_actual_salary_male
        from
            staff_tb st
            inner join a on st.staff_id = a.staff_id
        where
            staff_gender = 'male'
        group by
            department
    ),
    t3 as (
        select
            department,
            ifnull(round(avg(nd), 2), 0.00) average_actual_salary_female
        from
            staff_tb st
            inner join a on st.staff_id = a.staff_id
        where
            staff_gender = 'female'
        group by
            department
    )
select
    t1.department,
    ifnull(average_actual_salary, 0.00) average_actual_salary,
    ifnull(average_actual_salary_male, 0.00) average_actual_salary_male,
    ifnull(average_actual_salary_female, 0.00) average_actual_salary_female
from
    t1
    left join t2 on t1.department = t2.department
    left join t3 on t1.department = t3.department
order by
    average_actual_salary desc