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

select department
,round(avg(avg_sal),2) as average_actual_salary
,IFNULL(ROUND(AVG(IF(staff_gender='male',avg_sal,NULL)),2),0.00) as average_actual_salary_male
,IFNULL(ROUND(AVG(IF(staff_gender='female',avg_sal,NULL)),2),0.00) as average_actual_salary_female
from (select t.staff_id,department,staff_gender,normal_salary-dock_salary as avg_sal
from staff_tb t
join salary_tb t1 on t.staff_id=t1.staff_id) t
group by department
order by average_actual_salary desc