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

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