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

SELECT department,
round(avg(real_salary),2) AS average_actual_salary,
IFNULL(round(avg(CASE WHEN staff_gender = 'male' THEN real_salary END),2),0) AS average_actual_salary_male,
IFNULL(round(avg(CASE WHEN staff_gender = 'female' THEN real_salary END),2),0) AS average_actual_salary_female
FROM(    
    SELECT a.staff_id,a.staff_gender,a.department,b.normal_salary - b.dock_salary AS real_salary
    FROM staff_tb AS a
    JOIN salary_tb AS b ON a.staff_id = b.staff_id
) AS c
GROUP BY department
ORDER BY average_actual_salary DESC

这个题比较简单,我用了两层SELECT,第一层的先把两表连起来保留id，性别，部门和真实工资,第二层使用GROUP BY计算出平均薪资就好了,男女平均薪资在avg函数中加上CASE WHEN进行判断就好了