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

select 
department
,round(sum(s1.normal_salary-s1.dock_salary)/count(*),2) as average_actual_salary
,round(coalesce(sum(case when s.staff_gender='male' then normal_salary-dock_salary else 0 end)/count(case when s.staff_gender='male' then 1 end),0),2) as  average_actual_salary_male
,round(coalesce(sum(case when s.staff_gender='female' then normal_salary-dock_salary else 0 end)/count(case when s.staff_gender='female' then 1 end),0),2) as average_actual_salary_female
from staff_tb s
join salary_tb s1
on s.staff_id=s1.staff_id
group by department
order by average_actual_salary desc;