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

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

核心思路：使用CASE WHEN在SELECT中分别选择male/female的平均薪资。

注意：使用COALESCE(*，0）来处理某部门仅有一种性别的情况，填充为0。