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

SELECT
    st.department,
    ROUND(SUM(normal_salary - dock_salary)/COUNT(st.staff_id),2) average_actual_salary,
    ROUND(IFNULL(AVG(IF(st.staff_gender = 'male', normal_salary - dock_salary, NULL)),0),2) average_actual_salary_male,
    ROUND(IFNULL(AVG(IF(st.staff_gender = 'female', normal_salary - dock_salary, NULL)),0),2) average_actual_salary_female
FROM staff_tb st
JOIN salary_tb sa ON st.staff_id = sa.staff_id
GROUP BY st.department
ORDER BY average_actual_salary DESC