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

with base_name as(
    select
        sa.salary_id ,
        sa.staff_id ,
        round(sa.normal_salary - sa.dock_salary,3) as diff_salary
    from salary_tb sa 
)
select 
    st.department ,
    avg(ba.diff_salary) as avg_salary
from staff_tb st
inner join base_name ba
on ba.staff_id =st.staff_id
where diff_salary between 4000 and 30000
group by st.department
order by avg_salary desc