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

With actual as(
    select salary_id
    ,staff_id
    ,normal_salary-dock_salary as actual_salary
    from salary_tb
)

select
s.department
,coalesce(round(avg(a.actual_salary),2),0) as average_actual_salary
,coalesce(round(avg(case when s.staff_gender = 'male' then a.actual_salary end),2),0) as average_actual_salary_male
,coalesce(round(avg(case when s.staff_gender = 'female' then a.actual_salary end),2),0) as average_actual_salary_female
from staff_tb s
left join actual a
using (staff_id)
group by s.department
order by average_actual_salary desc