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

select 
    department,
    coalesce(average_actual_salary,'0.00') as average_actual_salary,
    coalesce(average_actual_salary_male,'0.00') as average_actual_salary_male,
    coalesce(average_actual_salary_female,'0.00') as average_actual_salary_female
from (
select
    department,
    round(avg(salary),2) as average_actual_salary,
    round(avg(if(staff_gender = 'male',salary, null )),2)as average_actual_salary_male,
    round(avg(if(staff_gender = 'female',salary, null )),2) as average_actual_salary_female
from
    (
        select
            a.staff_id,
            staff_name,
            staff_gender,
            department,
            (normal_salary - dock_salary) as salary
        from
            staff_tb a
            LEFT join salary_tb b on a.staff_id = b.staff_id
    ) tt
group by
    department
order by average_actual_salary desc
) t1