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

with
    total as (
        select
            department,
            round(avg(normal_salary - dock_salary), 2) as average_actual_salary
        from
            staff_tb st
            join salary_tb sl on st.staff_id = sl.staff_id
        group by
            department
    ),
    male_total as (
        select
            department,
            round(avg(normal_salary - dock_salary), 2) as average_actual_salary_male
        from
            staff_tb st
            join salary_tb sl on st.staff_id = sl.staff_id
        where
            staff_gender = 'male'
        group by
            department
    ),
    female_total as (
        select
            department,
            round(avg(normal_salary - dock_salary), 2) as average_actual_salary_female
        from
            staff_tb st
            join salary_tb sl on st.staff_id = sl.staff_id
        where
            staff_gender = 'female'
        group by
            department
    )

先建立三个临时表，分别计算总平均工资，男平均工资和女平均工资，当然会存在可能某个性别为空的情况，这里不用管。

select
    t.department,
    average_actual_salary,
    ifnull(average_actual_salary_male, 0.00) as average_actual_salary_male,
    ifnull(average_actual_salary_female, 0.00) as average_actual_salary_female
from
    total t
    left join male_total m on t.department = m.department
    left join female_total f on t.department = f.department
order by
    average_actual_salary desc;

然后用left join连接三个表，不能用join，因为如果某个性别为空的话，那一整个部门就会不显示出来，然后再用ifnull判断，如果为空就显示0.00。思路清晰。