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

With 
    t_salary AS(
    SELECT
    t1.department,
    ROUND(AVG(t2.normal_salary - t2.dock_salary),2) avg_salary
    FROM staff_tb t1
    INNER JOIN salary_tb t2
    ON t1.staff_id = t2.staff_id
    GROUP BY t1.department
    ),

    m_salary as(
    SELECT
    t1.department,
    IFNULL(ROUND(AVG(t2.normal_salary - t2.dock_salary),2),'0.00') average_actual_salary_male
    FROM staff_tb t1
    LEFT JOIN salary_tb t2
    ON t1.staff_id = t2.staff_id
    WHERE t1.staff_gender = 'male'
    GROUP BY t1.department
    ),

    f_salary as(
    SELECT
    t1.department,
    IFNULL(ROUND(AVG(t2.normal_salary - t2.dock_salary),2),'0.00') average_actual_salary_female
    FROM staff_tb t1
    LEFT JOIN salary_tb t2
    ON t1.staff_id = t2.staff_id
    WHERE t1.staff_gender = 'female'
    GROUP BY t1.department
    )

SELECT 
a1.department,
a1.avg_salary average_actual_salary,
IFNULL(b1.average_actual_salary_male,'0.00') average_actual_salary_male,
c1.average_actual_salary_female
FROM t_salary a1
LEFT JOIN m_salary b1
ON a1.department = b1.department
LEFT JOIN f_salary c1
ON a1.department = c1.department
ORDER BY average_actual_salary DESC