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

with table1(department,average_actual_salary) as (select department,round(avg(normal_salary-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)
select table1.department,average_actual_salary,COALESCE(average_actual_salary_male, 0) AS average_actual_salary_male,
COALESCE(average_actual_salary_female, 0) AS average_actual_salary_female
from table1
left join (select department,round(avg(normal_salary-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) table_male on table1.department=table_male.department
left join (select department,round(avg(normal_salary-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) table_female on table1.department=table_female.department
order by average_actual_salary desc