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

WITH t1 AS (
    SELECT
           b.department,
           ROUND(AVG(a.normal_salary - a.dock_salary),3) AS avg_salary
    FROM
         (SELECT * FROM salary_tb
         WHERE (normal_salary - dock_salary)
             BETWEEN 4000 AND 30000) a
    LEFT JOIN staff_tb b
    ON a.staff_id = b.staff_id
    GROUP BY b.department
    ORDER BY avg_salary DESC
)
SELECT * FROM t1;

# 记得提前筛选工资范围。