题解 | 统计各个部门平均薪资

WITH salary AS(
    SELECT 
        staff_id,
        normal_salary-dock_salary AS salary
    FROM salary_tb
    WHERE normal_salary-dock_salary between 4000 and 30000 #是按照实发工资进行剔除
)
SELECT 
    staff_tb.department,
    avg(salary.salary) AS avg_salary
FROM salary
LEFT JOIN staff_tb
ON salary.staff_id=staff_tb.staff_id
GROUP BY staff_tb.department
ORDER BY avg(salary.salary) DESC
;