# 构建出实发工资表格
with actual_salary as(
select st.staff_id,
       st.department,
       (sa.normal_salary-sa.dock_salary) as salary
from staff_tb st
join salary_tb sa on st.staff_id = sa.staff_id
)

# 筛选出计算范围内的实际工资，并且根据部门分组来看平均工资
select department,
        round(avg(salary),3) as avg_salary
from actual_salary
where salary between 4000 and 30000
group by department
order by avg_salary desc