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

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，从而逐层向下展开。

要对比“本人 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 先看前三层是否正常。