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

select t1.department, 
    round(ifnull(avg(t2.normal_salary-t2.dock_salary), 0), 2) as average_actual_salary,
    round(ifnull(avg(case when t1.staff_gender = 'male' then t2.normal_salary-t2.dock_salary else NULL end), 0), 2) as average_actual_salary_male,
    round(ifnull(avg(case when t1.staff_gender = 'female' then t2.normal_salary-t2.dock_salary else NULL end), 0), 2) as average_actual_salary_female
from staff_tb t1 join salary_tb t2
on t1.staff_id = t2.staff_id
group by t1.department
order by average_actual_salary desc