SQL 如何用递归查询实现“上下级员工薪资对比”

陆砚码

2026-10-24 • 👁 0人浏览过

SQL递归查询必要前提是数据库支持WITH RECURSIVE（如PostgreSQL、SQL Server等），且初始与递归成员列名类型严格一致，MySQL 5.7及更早版本不支持。什么是 SQL 递归查询的必要前提不是所有数据库都支持递归查询，

WITH RECURSIVE

是标准写法，但只在 PostgreSQL、SQL Server、SQLite 3.8.3+、Oracle（用

START WITH ... CONNECT BY

）、MySQL 8.0+ 中可用。MySQL 5.7 或更早版本直接不支持，强行写会报错

ERROR 1064

。PostgreSQL 要求初始查询（anchor member）和递归查询（recursive member）的列名、类型严格一致，否则报

UNION types text and integer cannot be matched

。确认你的环境支持前，先运行一句测试：

WITH RECURSIVE t(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM t WHERE n < 3) SELECT * FROM t;

能返回 1/2/3 就说明递归可用。构造员工上下级关系表的关键字段递归依赖明确的自关联结构，必须有：

employee_id

、

manager_id

（可为 NULL 表示顶层领导），以及要对比的

salary

。别用

parent_id

或

boss_id

这类模糊命名，容易在 JOIN 或递归中漏判 NULL。常见错误是把

manager_id

设为 NOT NULL，导致 CEO 数据无法进入初始查询；或者把

manager_id

类型设成 INT 而实际存了字符串（比如 'NULL' 文本），导致递归断链。建表示例（PostgreSQL）：

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name TEXT,
  manager_id INT,
  salary NUMERIC(10,2)
);

写递归 CTE 计算每人的直属上级与薪资差核心是两段拼接：初始查询选所有无上级（

manager_id IS NULL

）或需作为起点的人；递归段用

JOIN

回原表，匹配

manager_id = e.employee_id

，从而逐层向下展开。 MySQL（Linux） MySQL 9.6.0是面向Linux平台的2026年创新版本，核心架构迎来重大革新。其将外键约束与级联操作从InnoDB引擎层上移至SQL层，确保所有数据变更均被完整记录至Binlog，彻底解决了CDC（变更数据捕获）与主从复制中的数据不一致难题。此外，该版本引入container_aware启动选项以原生适配容器环境，并对审计日志进行了组件化重构，为追求极致数据一致性与云原生体验的开发者提供了全新选择。下载要对比“本人 vs 直属上级”薪资，递归结果里得保留上级的

salary

，所以递归 CTE 中必须显式 SELECT 上级字段，不能只 SELECT 下级：

WITH RECURSIVE emp_tree AS (
  -- anchor: 所有顶层管理者（无 manager）
  SELECT employee_id, name, manager_id, salary, 
         NULL::NUMERIC AS manager_salary,
         0 AS level
  FROM employees 
  WHERE manager_id IS NULL
UNION ALL

-- recursive: 每个员工连接其 manager 的信息 SELECT e.employee_id, e.name, e.manager_id, e.salary, m.salary AS manager_salary, et.level + 1 FROM employees e INNER JOIN emp_tree et ON e.manager_id = et.employee_id INNER JOIN employees m ON e.manager_id = m.employee_id ) SELECT name, salary, manager_salary, COALESCE(salary - manager_salary, 0) AS diff_vs_manager FROM emp_tree WHERE manager_salary IS NOT NULL; -- 排除顶层，只看有上级的人

注意点：

INNER JOIN emp_tree et ON e.manager_id = et.employee_id

是递归推进的关键，不能写成

ON e.employee_id = et.manager_id

必须额外

JOIN employees m

拿上级薪资，因为

emp_tree

当前行只含本级数据，不含上级的

salary

PostgreSQL 中

NULL::NUMERIC

是为了统一 anchor 和 recursive 的列类型，否则 UNION 失败性能和层级深度的实际限制递归没有自动终止机制，如果数据存在环（比如 A 管 B，B 管 C，C 又管 A），查询会无限循环直到超时或达到数据库默认最大深度（PostgreSQL 默认

max_recursion_depth = 100

）。MySQL 8.0 用

MAXRECURSION

限制，但需手动加

OPTION (MAXRECURSION 10)

。真实业务中，组织层级 rarely 超过 10 层。若查出来结果为空，优先检查：

manager_id

是否全为 NULL（数据没录好）是否误把

employee_id

和

manager_id

类型设反（比如 manager_id 是 VARCHAR，employee_id 是 INT）递归段漏写了

WHERE

条件，导致笛卡尔爆炸层级过深时，

level

字段比单纯嵌套子查询更易调试——你可以加

WHERE level 先看前三层是否正常。

相关文章