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

select 
t1.department
,round(avg(t2.normal_salary - t2.dock_salary),2) as average_actual_salary
,round(ifnull(avg(if(t1.staff_gender = 'male',t2.normal_salary - t2.dock_salary,null )),0),2) as average_actual_salary_male
,round(ifnull(avg(if(t1.staff_gender = 'female',t2.normal_salary - t2.dock_salary,null )),0),2) as average_actual_salary_female
from staff_tb t1 
left join salary_tb t2
on t1.staff_id = t2.staff_id
group by t1.department
order by average_actual_salary desc