HR示例数据库员工薪资等级差查询SQL编写咨询

• 聚合函数用法不合法：直接在SELECT子句中混用普通列salary和聚合函数AVG(salary)，既没有写GROUP BY分组，也没有用窗口函数指定聚合范围，执行时会直接报语法错误；且此处未限定平均薪资的计算范围是「同岗位员工」，计算逻辑不符合需求。
• 薪资等级差逻辑错误：原逻辑仅处理了薪资高于平均的分支，低于、等于平均的场景会返回NULL；没有按要求添加+/-的正负标识，还错误将岗位薪资区间跨度（max_salary - min_salary）作为等级差的返回值，完全没有体现员工个人薪资和岗位平均薪资的差值关系。
• 输出字段不符合要求：需求要求输出员工姓名，原写法仅拆分输出名和姓，没有做拼接处理。
• 表关联写法不规范：使用逗号做隐式内连接，可读性差，维护成本高，推荐使用显式JOIN写法。

写法1：兼容所有SQL版本的通用写法 #

通过子查询先统计每个岗位的平均薪资，再关联员工表、岗位表计算差值：

SELECT
  CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
  j.job_title AS position,
  e.salary,
  CONCAT(
    CASE WHEN e.salary > job_avg.avg_sal THEN '+' ELSE '-' END,
    ABS(ROUND(e.salary - job_avg.avg_sal, 2))
  ) AS salary_class_difference
FROM hr.employees e
INNER JOIN hr.jobs j
  ON e.job_id = j.job_id
INNER JOIN (
  SELECT job_id, AVG(salary) AS avg_sal
  FROM hr.employees
  GROUP BY job_id
) job_avg
  ON e.job_id = job_avg.job_id;

写法2：支持窗口函数的简化写法（MySQL8+、Oracle、PostgreSQL等适用） #

用窗口函数按岗位分区计算平均薪资，省去子查询关联步骤，执行效率更高：

SELECT
  CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
  j.job_title AS position,
  e.salary,
  CONCAT(
    CASE WHEN e.salary > AVG(e.salary) OVER (PARTITION BY e.job_id) THEN '+' ELSE '-' END,
    ABS(ROUND(e.salary - AVG(e.salary) OVER (PARTITION BY e.job_id), 2))
  ) AS salary_class_difference
FROM hr.employees e
INNER JOIN hr.jobs j
  ON e.job_id = j.job_id;

字段说明：salary_class_difference字段会先判断员工薪资和同岗位平均薪资的高低，高则前缀加+，低则前缀加-，后面拼接两者差值的绝对值，结果示例：+1200、-850.5。如果需要调整差值计算规则，比如对比岗位薪资区间中值、区间上下限，只需要替换CASE判断和差值计算部分的参照值即可。

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