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

select
    department,
    average_actual_salary,
    if(average_actual_salary_male is null,0.00,average_actual_salary_male) as average_actual_salary_male,
    if(average_actual_salary_female is null,0.00,average_actual_salary_female) as average_actual_salary_female
from
    (
        select
            department,
            round(avg(normal_salary - dock_salary), 2) as average_actual_salary,
            round(
                sum(
                    case
                        when staff_gender = 'male' then (normal_salary - dock_salary)
                        else 0
                    end
                ) / sum(
                    case
                        when staff_gender = 'male' then 1
                        else 0
                    end
                ),
                2
            ) as average_actual_salary_male,
            round(
                sum(
                    case
                        when staff_gender = 'female' then (normal_salary - dock_salary)
                        else 0
                    end
                ) / sum(
                    case
                        when staff_gender = 'female' then 1
                        else 0
                    end
                ),
                2
            ) as average_actual_salary_female
        from
            staff_tb
            join salary_tb using (staff_id)
        group by
            department
    ) as t 
order by
    average_actual_salary desc

这个写法效率上不是最优的，但是思路比较清晰，纯用来速通做题