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

with t1 as-- 求出 部门-性别 下的实发工资
(select
        staff_gender,
        normal_salary-dock_salary as actual_salary,
        department
from staff_tb st  
join salary_tb sa on sa.staff_id=st.staff_id)

select 
    department,
    round(avg(actual_salary),2) as average_actual_salary,
    ifnull(round(avg(if(staff_gender='male',actual_salary,null)),2),0.00) as average_actual_salary_male,
    ifnull(round(avg(if(staff_gender='female',actual_salary,null)),2),0.00) as average_actual_salary_female
from
    t1
group by department 
order by average_actual_salary desc;