统计部门员工平均工资排名前十的部门名称和平均工资

Question

统计部门员工平均工资排名前十的部门名称和平均工资
统计部门员工平均工资排名前十的部门名称和平均工资

Answer 1

SELECT dept_name, AVG(salary) AS avg_salary
FROM (
    SELECT d.dept_name, e.salary, ROW_NUMBER() OVER (PARTITION BY d.dept_name ORDER BY e.salary DESC) AS rank
    FROM employees e JOIN departments d ON e.dept_id = d.dept_id
) t
WHERE t.rank <= 10
GROUP BY dept_name
ORDER BY AVG(salary) DESC;