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

with t as (
    select department, staff_gender, normal_salary-dock_salary as actual_salary
    from staff_tb a join salary_tb b on a.staff_id=b.staff_id
), t_average_actual_salary as (
    select department, round(avg(actual_salary), 2) as average_actual_salary
    from t
    group by department
), t_average_actual_salary_male as (
    select department,  round(avg(actual_salary), 2) as average_actual_salary_male
    from t
    where staff_gender='male'
    group by department
), t_average_actual_salary_female as (
    select department,  round(avg(actual_salary), 2) as average_actual_salary_female
    from t
    where staff_gender='female'
    group by department
)
select t1.department, average_actual_salary, 
ifnull(average_actual_salary_male, 0.0) as average_actual_salary_male, 
ifnull(average_actual_salary_female, 0.0) as average_actual_salary_female
from t_average_actual_salary t1
left join t_average_actual_salary_male t2 on t1.department=t2.department
left join t_average_actual_salary_female t3 on t1.department=t3.department
order by average_actual_salary desc