select 
department
,round(avg(t2.ts),3) as avg_salary
from staff_tb t1
right join (select
            *
            ,(normal_salary - dock_salary) as ts
            from salary_tb
            where (normal_salary - dock_salary) between 4000 and 30000
)t2
on t1.staff_id = t2.staff_id
group by 1
order by 2 desc

where 语句中不能出现新设置的列名称