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

select
    b.department,
    round(avg(c.normal_salary - c.dock_salary),3) as avg_salary
from
    (select
        staff_id,
        round(avg(normal_salary - dock_salary),3) as avg_salary
    from
        salary_tb
    group by 
        1
    having
        avg(normal_salary - dock_salary) between 4000 and 30000) a
inner join
    staff_tb b on a.staff_id = b.staff_id
left join
    salary_tb c on a.staff_id = c.staff_id
group by 
    1
order by 
    2 desc