题解 | 统计各个部门平均薪资
统计各个部门平均薪资
https://www.nowcoder.com/practice/4722fdf89a4c43eebb58d61a19ccab31
WITH t AS (
SELECT staff_id,normal_salary-dock_salary AS salary
FROM salary_tb
WHERE normal_salary-dock_salary >=4000 AND normal_salary-dock_salary<=30000
)
SELECT t1.department ,ROUND(AVG(salary),3) AS avg_salary
FROM t
LEFT JOIN staff_tb t1 ON t1.staff_id=t.staff_id
GROUP BY t1.department
ORDER BY avg_salary DESC
