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

with t as (
    select
    department
    ,round(avg(normal_salary - dock_salary), 2) average_actual_salary
    from staff_tb stt
    join salary_tb sat
    on stt.staff_id = sat.staff_id
    group by department)
, t2 as (
    select
    department
    ,round(avg(normal_salary - dock_salary), 2) average_actual_salary_male
    from staff_tb stt
    join salary_tb sat
    on stt.staff_id = sat.staff_id
    where staff_gender = 'male'
    group by department
)
, t3 as (
    select
    department
    ,round(avg(normal_salary - dock_salary), 2) average_actual_salary_female
    from staff_tb stt
    join salary_tb sat
    on stt.staff_id = sat.staff_id
    where staff_gender = 'female'
    group by department
)
select 
t.department department
,average_actual_salary 
,if(average_actual_salary_male is null, '0.00', average_actual_salary_male) average_actual_salary_male
,if(average_actual_salary_female is null, '0.00', average_actual_salary_female)   
average_actual_salary_female
from t 
left join t2 on t.department = t2.department
left join t3 on t.department = t3.department
order by average_actual_salary desc