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

WITH t AS (
    SELECT staff_id,normal_salary-dock_salary AS salary
    FROM salary_tb
    WHERE normal_salary-dock_salary >=4000 AND normal_salary-dock_salary<=30000
)
SELECT t1.department ,ROUND(AVG(salary),3) AS avg_salary
FROM t 
LEFT JOIN staff_tb t1 ON t1.staff_id=t.staff_id
GROUP BY t1.department
ORDER BY avg_salary  DESC