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

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

select 
    t2.department,
    average_actual_salary,
    coalesce(average_actual_salary_male,0.00) as average_actual_salary_male,
    coalesce(average_actual_salary_female,0.00) as average_actual_salary_female
from t2
left join t3 on t2.department=t3.department
left join t4 on t2.department=t4.department
order by average_actual_salary desc