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

select
    t1.department,
    cast(avg(t2.normal_salary-t2.dock_salary) as decimal(10,3)) as avg_salary
from 
    staff_tb t1
left join 
    salary_tb t2
on 
    t1.staff_id=t2.staff_id
where-----原本想在group by后用having来做限定，但不行，原因是因为HAVING 子句只用于在分组之后过滤分组结果，通常后面要跟聚合函数或者 GROUP BY 中指定的列
    (t2.normal_salary-t2.dock_salary) between 4000 and 30000
group by 
    t1.department 
order by
    avg_salary desc