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

select t1.department,round(avg(t2.salary),3) as avg_salary
from(
select distinct staff_id,department
from staff_tb
)t1
left join(
   select staff_id,normal_salary - dock_salary as  salary
   from salary_tb 
   where normal_salary - dock_salary >=4000  and normal_salary - dock_salary <=30000

)t2 using(staff_id)
group by t1.department 
order by avg_salary desc