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

select
     st.department,
     round(avg(sat.normal_salary - sat.dock_salary),2) as average_actual_salary,
     -- 如果不是男性，值会变为null值，算平均数时不会加入到人头数上，coalesce返回实数，排除空值
     round(coalesce(avg(case when st.staff_gender = 'male' then sat.normal_salary - sat.dock_salary end),0),2) as average_actual_salary_male,
     round(coalesce(avg(case when st.staff_gender = 'female' then sat.normal_salary - sat.dock_salary end),0),2) as average_actual_salary_female
     from staff_tb st
     inner join salary_tb  sat 
     on sat.staff_id = st.staff_id
     group by st.department
     order by average_actual_salary desc