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

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,
	#迭代表筛选出所有staff_gender为male的行，计算出行对应的实际工资，然后对所有行求平均。若为空值则返回0。
    ROUND(COALESCE(AVG(CASE WHEN staff_gender = 'female' THEN normal_salary - dock_salary END), 0), 2) AS average_actual_salary_female
	#迭代表筛选出所有staff_gender为female的行，计算出行对应的实际工资，然后对所有行求平均。若为空值则返回0。
FROM 
    salary_tb
    JOIN staff_tb USING(staff_id)
GROUP BY 
    department
ORDER BY 
    average_actual_salary DESC