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

with avg_all as(
    select
    department,round(sum(normal_salary-dock_salary)/count(*),2) as average_actual_salary
    from staff_tb s1
    inner join salary_tb s2 on s1.staff_id=s2.staff_id
    group by department
),

avg_male as(
    select
    department,round(sum(normal_salary-dock_salary)/count(*),2) as average_actual_salary_male
    from staff_tb s1
    inner join salary_tb s2 on s1.staff_id=s2.staff_id
    where staff_gender='male'
    group by department
),

avg_female as(
select
    department,round(sum(normal_salary-dock_salary)/count(*),2) as average_actual_salary_female
    from staff_tb s1
    inner join salary_tb s2 on s1.staff_id=s2.staff_id
    where staff_gender='female'
    group by department
)

select
a1.department as department,average_actual_salary,if(average_actual_salary_male is null,0.00,average_actual_salary_male) as average_actual_salary_male,if(average_actual_salary_female is null,0.00,average_actual_salary_female) as average_actual_salary_female
from avg_all a1
left join avg_male a2 on a1.department=a2.department
left join avg_female a3 on a1.department=a3.department
order by average_actual_salary desc;