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

with
tb1 as(
    select staff_id, staff_gender, department from staff_tb
),
tb2 as(
    select staff_id, normal_salary - dock_salary as actual_salary from salary_tb
),
tb3 as(
    select department, ifnull(round(avg(actual_salary), 2), 0) as average_actual_salary from tb1
    join tb2 on tb1.staff_id = tb2.staff_id
    group by department
    order by average_actual_salary desc   
),
tb4 as(
    select department, ifnull(round(avg(actual_salary), 2), 0) as 
        average_actual_salary_male from tb1
    left join tb2 on tb1.staff_id = tb2.staff_id and tb1.staff_gender = 'male'
    group by department
),
tb5 as(
    select department, ifnull(round(avg(actual_salary), 2), 0) as 
        average_actual_salary_female from tb1
    left join tb2 on tb1.staff_id = tb2.staff_id and tb1.staff_gender = 'female'
    group by department
)
select tb3.department, tb3.average_actual_salary, tb4.average_actual_salary_male, 
    tb5.average_actual_salary_female from tb3
left join tb4 on tb3.department = tb4.department
left join tb5 on tb3.department = tb5.department