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

select
    department,
    avg(实发薪资) avg_salary
from
    (
        select
            department,
            normal_salary - dock_salary 实发薪资
        from
            test.staff_tb st
            join test.salary_tb sat on st.staff_id = sat.staff_id
        where
            normal_salary - dock_salary between 4000 and 30000
    ) a
group by
    department
order by
    avg_salary desc