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

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