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

with
    department_actual_salary as (
        select
            staff_tb.department,
            ifnull(
                round(
                    (
                        sum(salary_tb.normal_salary) - sum(salary_tb.dock_salary)
                    ) / count(staff_tb.department),
                    2
                ),
                0.00
            ) as average_actual_salary
        from
            staff_tb
            left join salary_tb on salary_tb.staff_id = staff_tb.staff_id
        group by
            staff_tb.department
    ),
    male_salary_tb as (
        select
            staff_tb.department,
            round(
                (
                    sum(salary_tb.normal_salary) - sum(salary_tb.dock_salary)
                ) / count(staff_tb.department),
                2
            ) as average_actual_salary_male
        from
            staff_tb
            left join salary_tb on salary_tb.staff_id = staff_tb.staff_id
        where
            staff_tb.staff_gender = 'male'
        group by
            staff_tb.department
    ),
    female_salary_tb as (
        select
            staff_tb.department,
            round(
                (
                    sum(salary_tb.normal_salary) - sum(salary_tb.dock_salary)
                ) / count(staff_tb.department),
                2
            ) as average_actual_salary_female
        from
            staff_tb
            join salary_tb on salary_tb.staff_id = staff_tb.staff_id
        where
            staff_tb.staff_gender = 'female'
        group by
            staff_tb.department
    )
select
    department_actual_salary.department,
    department_actual_salary.average_actual_salary,
    ifnull(male_salary_tb.average_actual_salary_male, 0.00) as average_actual_salary_male,
    ifnull(female_salary_tb.average_actual_salary_female, 0.00) as average_actual_salary_female
from
    department_actual_salary
    left join male_salary_tb on department_actual_salary.department = male_salary_tb.department
    left join female_salary_tb on department_actual_salary.department = female_salary_tb.department
order by
    department_actual_salary.average_actual_salary desc