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

SELECT
    department,
    ROUND(AVG(real_salary), 3) AS avg_salary
FROM(
    SELECT
        department,
        normal_salary - dock_salary AS real_salary
    FROM staff_tb s
    JOIN salary_tb sa
    ON s.staff_id = sa.staff_id
) AS tb1
WHERE real_salary BETWEEN 4000 AND 30000
GROUP BY department
ORDER BY avg_salary DESC