【大厂真题】SQL20题解 | 统计各个部门平均薪资

# 需求：实发薪资=标准薪资-扣除薪资，统计平均薪资要求剔除薪资小于4000和大于30000的员工
# 平均实发薪资（保留3位小数）
# 按照平均实发薪资降序排序

SELECT a.department,avg(ROUND(b.salary,3)) as avg_salary
from staff_tb  a
inner join 
(
    SELECT staff_id,normal_salary-dock_salary as salary # 注意别名
    from 
    salary_tb
    where (normal_salary-dock_salary) <=30000
    and (normal_salary-dock_salary) >=4000
) b
on a.staff_id=b.staff_id
group by a.department
order by avg_salary desc