题解 | 统计各个部门平均薪资
WITH t1 AS (
SELECT
staff_id,
normal_salary - dock_salary as actual_salary
FROM salary_tb
)
SELECT
department,
round(avg(actual_salary),3) as avg_salary
FROM staff_tb JOIN t1 USING(staff_id)
WHERE actual_salary BETWEEN 4000 AND 30000
GROUP BY department
ORDER BY avg_salary DESC
投递上海得物信息集团有限公司等公司6个岗位