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