如何查询薪资高于所属部门平均薪资的所有员工

预置表结构说明 #

假设业务库存在两张核心表，字段定义如下：

• employees（员工表）：存储员工基础信息，核心字段为emp_id(员工唯一ID)、emp_name(员工姓名)、dept_id(所属部门ID)、salary(员工月薪资)
• departments（部门表）：存储部门基础信息，核心字段为dept_id(部门唯一ID)、dept_name(部门名称)

实现方法 #

方法1：子查询实现（全版本兼容） #

适配所有主流关系型数据库（MySQL 5.x、Oracle、PostgreSQL、SQL Server等都可直接运行），逻辑直观易维护：

SELECT 
  e.emp_id,
  e.emp_name,
  d.dept_name,
  e.salary,
  dept_avg.avg_salary AS department_average_salary
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
INNER JOIN (
  -- 子查询计算每个部门的平均薪资
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) dept_avg ON e.dept_id = dept_avg.dept_id
WHERE e.salary > dept_avg.avg_salary
ORDER BY e.dept_id, e.salary DESC;

方法2：窗口函数实现（高性能版本） #

适配支持窗口函数的数据库版本（MySQL 8.0+、PostgreSQL 9.4+、Oracle 11g+等），只需扫描一次员工表，数据量越大性能优势越明显：

SELECT 
  emp_id,
  emp_name,
  dept_name,
  salary,
  department_average_salary
FROM (
  SELECT 
    e.emp_id,
    e.emp_name,
    d.dept_name,
    e.salary,
    -- 按部门分组计算平均薪资
    AVG(e.salary) OVER(PARTITION BY e.dept_id) AS department_average_salary
  FROM employees e
  LEFT JOIN departments d ON e.dept_id = d.dept_id
) temp
WHERE salary > department_average_salary
ORDER BY dept_id, salary DESC;

落地注意事项 #

• 数据过滤：查询前可先过滤salary为NULL、dept_id为空的无效数据，避免平均薪资计算出现偏差
• 索引优化：如果员工表数据量超过10万条，建议给employees表的dept_id、salary字段创建联合索引，查询速度可提升10倍以上
• 精度处理：如果薪资为浮点型，建议对平均薪资保留2位小数后再对比，避免浮点精度问题导致的判断错误，可使用ROUND(AVG(salary),2)处理

内容的提问来源于stack exchange，提问作者BABU