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

SELECT
    department,
    ROUND(AVG(normal_salary - dock_salary), 2) AS average_actual_salary,
    ROUND(COALESCE(AVG(CASE WHEN staff_gender = 'male' THEN normal_salary-dock_salary END), 0), 2) AS average_actual_salary_male,
    ROUND(COALESCE(AVG(CASE WHEN staff_gender='female' THEN normal_salary-dock_salary END), 0), 2) AS average_actual_salary_female
FROM staff_tb JOIN salary_tb
ON staff_tb.staff_id = salary_tb.staff_id
GROUP BY department
ORDER BY average_actual_salary DESC