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

#求出实发工资，并对条件进行过滤
with t as(
select
staff_id,
normal_salary - dock_salary sfgz
from salary_tb
where normal_salary - dock_salary >= 4000 and 
normal_salary - dock_salary <= 30000
)

#按部门分组，求均值
select
department,
avg(sfgz) avg_salary
from t left join staff_tb s on t.staff_id = s.staff_id
group by department
order by avg_salary desc