with t as (
select st.staff_id, staff_name, staff_gender, department, normal_salary-dock_salary as salary,
       case when staff_gender = "male" then normal_salary-dock_salary else null end as male_salary,
       case when staff_gender = "female" then normal_salary-dock_salary else null end as female_salary
from staff_tb st
join salary_tb sa
on st.staff_id = sa.staff_id)

# 注意:当出现不存在某类型员工,平均薪资用0.00表示,用0填充None
select department, round(coalesce(avg(salary),0),2) as average_actual_salary,
                   round(coalesce(avg(male_salary),0),2) as average_actual_salary_male,
                   round(coalesce(avg(female_salary),0),2)  as average_actual_salary_female
from t
group by department
order by average_actual_salary desc