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

with
t1 as(
    select
        staff_id,
        department,
        staff_gender,
        normal_salary-dock_salary as actual_salary
    from
        salary_tb left join staff_tb using(staff_id)
)
,
t2 as(
    select
        department,
        round(avg(actual_salary),2) as average_actual_salary
    from
        t1
    group by
        department
)
,
t3 as(
    select
        department,
        round(avg(actual_salary),2) as average_actual_salary_male
    from
        t1
    where
        staff_gender='male'
    group by
        department
)
,
t4 as(
    select
        department,
        round(avg(actual_salary),2) as average_actual_salary_female
    from
        t1
    where
        staff_gender='female'
    group by
        department
)

select
    department,
    average_actual_salary,
    (
        case
            when average_actual_salary_male is null then 0.00
            else average_actual_salary_male
        end
    ) as average_actual_salary_male,
    (
        case
            when average_actual_salary_female is null then 0.00
            else average_actual_salary_female
        end
    ) as average_actual_salary_female
from
    t2
    left join t3 using(department)
    left join t4 using(department)
order by
    average_actual_salary desc