CEO (1)
├── VP Eng (2)
│ └── Eng Lead (4)
│ └── Senior Dev (5)
│ └── Junior Dev (6)
│ └── Intern (9)
├── VP Sales (3)
│ └── Sales Lead (7)
│ └── Sales Rep (8)
└── VP Product (10)
方案 1：固定深度多层 LEFT JOIN
适用场景
已知最大层级数（大多数企业组织 ≤ 10 层）
表结构为简单邻接表（
id
id
,
parent_id
parent_id
）
不想修改现有数据模型
向上查找所有上级
SELECT
e.emp_id,
e.name AS employee,
m1.name AS manager_l1,
m2.name AS manager_l2,
m3.name AS manager_l3,
m4.name AS manager_l4,
m5.name AS manager_l5,
m6.name AS manager_l6
FROM org e
LEFT JOIN org m1 ON e.manager_id = m1.emp_id
LEFT JOIN org m2 ON m1.manager_id = m2.emp_id
LEFT JOIN org m3 ON m2.manager_id = m3.emp_id
LEFT JOIN org m4 ON m3.manager_id = m4.emp_id
LEFT JOIN org m5 ON m4.manager_id = m5.emp_id
LEFT JOIN org m6 ON m5.manager_id = m6.emp_id
ORDER BY e.emp_id;
输出示例:
emp_id
employee
manager_l1
manager_l2
manager_l3
manager_l4
manager_l5
1
CEO
NULL
NULL
NULL
NULL
NULL
5
Senior Dev
Eng Lead
VP Eng
CEO
NULL
NULL
9
Intern
Junior Dev
Senior Dev
Eng Lead
VP Eng
CEO
计算层级深度
SELECT
e.emp_id,
e.name,
-- 计算实际深度: 从第一个非 NULL 的 manager 列判断
CASE
WHEN m6.name IS NOT NULL THEN 6
WHEN m5.name IS NOT NULL THEN 5
WHEN m4.name IS NOT NULL THEN 4
WHEN m3.name IS NOT NULL THEN 3
WHEN m2.name IS NOT NULL THEN 2
WHEN m1.name IS NOT NULL THEN 1
ELSE 0
END AS depth
FROM org e
LEFT JOIN org m1 ON e.manager_id = m1.emp_id
LEFT JOIN org m2 ON m1.manager_id = m2.emp_id
LEFT JOIN org m3 ON m2.manager_id = m3.emp_id
LEFT JOIN org m4 ON m3.manager_id = m4.emp_id
LEFT JOIN org m5 ON m4.manager_id = m5.emp_id
LEFT JOIN org m6 ON m5.manager_id = m6.emp_id;
-- 插入根节点
INSERT INTO org_path VALUES (1, 'CEO', 0, '/0001/');
-- 插入子节点（路径 = 父路径 + 自身 ID）
INSERT INTO org_path
SELECT 2, 'VP Eng', 1, path || '/0002/' FROM org_path WHERE emp_id = 1;
查询模式 1：向下查找子树
-- 查找 CEO 的所有下级
SELECT emp_id, name, path,
(LENGTH(path) - LENGTH(REPLACE(path, '/', '')) - 1) AS depth
FROM org_path
WHERE path LIKE '/0001/%' AND emp_id != 1
ORDER BY path;
查询模式 2：向上查找祖先链
-- 查找 Intern (emp_id=9) 的所有上级
WITH levels AS (
SELECT 1 AS lvl UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
)
SELECT
l.lvl AS level,
CAST(SPLIT_PART('/0001/0002/0004/0005/0006/0009/', '/', l.lvl + 1) AS BIGINT) AS ancestor_id
FROM levels l
WHERE SPLIT_PART('/0001/0002/0004/0005/0006/0009/', '/', l.lvl + 1) != ''
ORDER BY l.lvl;
查询模式 3：层级树格式化输出
SELECT
emp_id,
REPEAT(' ', (LENGTH(path) - LENGTH(REPLACE(path, '/', '')) - 2)) ||
CASE WHEN (LENGTH(path) - LENGTH(REPLACE(path, '/', '')) - 1) > 1 THEN '└─ ' ELSE '' END ||
name AS tree_view,
(LENGTH(path) - LENGTH(REPLACE(path, '/', '')) - 1) AS level
FROM org_path
ORDER BY path;
emp_id | tree_view
-------+-----------------------
1 | CEO
2 | └─ VP Eng
4 | └─ Eng Lead
5 | └─ Senior Dev
6 | └─ Junior Dev
9 | └─ Intern
3 | └─ VP Sales
7 | └─ Sales Lead
8 | └─ Sales Rep
10 | └─ VP Product
查询模式 4：查找直接子节点
-- 查找 VP Eng (emp_id=2) 的直接下级
SELECT emp_id, name
FROM org_path
WHERE path LIKE '/0001/0002/%'
AND emp_id != 2
AND (LENGTH(path) - LENGTH(REPLACE(path, '/', '')) - 1) = 3; -- 父深度 + 1
优缺点
优点
缺点
单表存储，查询灵活
需要维护路径字段
子树查询只需
LIKE
LIKE
移动子树需更新所有后代路径
自然排序保证层级顺序
路径长度受 VARCHAR 限制
方案 3：闭包表（Closure Table）
适用场景
频繁查询层级关系（如权限判断、汇报线查询）
可接受额外存储开销
需要高效层级聚合
表结构设计
-- 原始邻接表
CREATE TABLE org (
emp_id BIGINT,
name VARCHAR,
manager_id BIGINT
);
-- 闭包表：存储所有 (ancestor, descendant, depth) 关系
CREATE TABLE org_closure (
ancestor BIGINT, -- 祖先节点 ID
descendant BIGINT, -- 后代节点 ID
depth INT -- 层级深度（0=自身）
);
构建闭包表（SQL 展开）
-- 通过多次自 JOIN 展开所有层级关系
WITH
l0 AS (SELECT emp_id AS ancestor, emp_id AS descendant, 0 AS depth FROM org),
l1 AS (SELECT manager_id AS ancestor, emp_id AS descendant, 1 AS depth FROM org WHERE manager_id > 0),
l2 AS (SELECT l1a.ancestor, l1b.descendant, 2 AS depth FROM l1 l1a JOIN l1 l1b ON l1a.descendant = l1b.ancestor),
l3 AS (SELECT l1a.ancestor, l2b.descendant, 3 AS depth FROM l1 l1a JOIN l2 l2b ON l1a.descendant = l2b.ancestor),
l4 AS (SELECT l1a.ancestor, l3b.descendant, 4 AS depth FROM l1 l1a JOIN l3 l3b ON l1a.descendant = l3b.ancestor),
l5 AS (SELECT l1a.ancestor, l4b.descendant, 5 AS depth FROM l1 l1a JOIN l4 l4b ON l1a.descendant = l4b.ancestor)
INSERT INTO org_closure
SELECT * FROM l0 UNION ALL SELECT * FROM l1 UNION ALL SELECT * FROM l2
UNION ALL SELECT * FROM l3 UNION ALL SELECT * FROM l4 UNION ALL SELECT * FROM l5;
⚠️ 注意：展开层数需 ≥ 最大层级深度。每增加一层需额外一个 JOIN CTE。
查询模式 1：查找子树
SELECT c.descendant, d.name, c.depth
FROM org_closure c
JOIN org d ON c.descendant = d.emp_id
WHERE c.ancestor = 1 AND c.depth > 0
ORDER BY c.depth, c.descendant;
查询模式 2：查找祖先链
SELECT c.ancestor, a.name, c.depth
FROM org_closure c
JOIN org a ON c.ancestor = a.emp_id
WHERE c.descendant = 9
ORDER BY c.depth;
查询模式 3：层级聚合
-- 计算每个管理者的直接 + 间接下属总数
SELECT
c.ancestor,
a.name AS manager_name,
COUNT(DISTINCT c.descendant) AS total_reports
FROM org_closure c
JOIN org a ON c.ancestor = a.emp_id
GROUP BY c.ancestor, a.name
ORDER BY total_reports DESC;
输出:
ancestor
manager_name
total_reports
1
CEO
10
2
VP Eng
5
4
Eng Lead
4
5
Senior Dev
3
闭包表维护
-- 插入新节点时更新闭包表
-- 假设新员工 (emp_id=11) 是 Senior Dev (emp_id=5) 的下属
INSERT INTO org_closure
SELECT c.ancestor, 11, c.depth + 1
FROM org_closure c
WHERE c.descendant = 5
UNION ALL
SELECT 11, 11, 0; -- 自身关系
-- 删除节点时清理闭包表
DELETE FROM org_closure
WHERE descendant IN (
SELECT descendant FROM org_closure WHERE ancestor = 6
);
-- 错误: 只改 manager_id，路径未更新
UPDATE org_path SET manager_id = 3 WHERE emp_id = 4;
-- 正确: 同时更新自身和所有后代路径
UPDATE org_path
SET path = REPLACE(path, '/0001/0002/', '/0001/0003/')
WHERE path LIKE '/0001/0002/%';
4.
WITH RECURSIVE
WITH RECURSIVE
语法不支持
-- 不支持
WITH RECURSIVE tree AS (...) SELECT * FROM tree;
-- 替代: 使用本文三种方案之一
实际应用：产品分类树
-- 产品分类表（路径枚举法）
CREATE TABLE product_category (
category_id BIGINT,
name VARCHAR,
parent_id BIGINT,
path VARCHAR,
sort_order INT
);
-- 查询某分类下的所有产品（含子分类）
SELECT p.*
FROM products p
JOIN product_category c ON p.category_id = c.category_id
WHERE c.path LIKE (
SELECT path FROM product_category WHERE category_id = 100
) || '%'
ORDER BY c.path, p.sort_order;
实际应用：BOM 物料清单
-- BOM 闭包表
CREATE TABLE bom_closure (
parent_part_id BIGINT,
child_part_id BIGINT,
depth INT,
quantity DECIMAL(10,2) -- 单层用量
);
-- 计算成品所需的所有原材料总量
SELECT
bc.child_part_id,
p.name AS part_name,
SUM(bc.quantity) AS total_quantity
FROM bom_closure bc
JOIN parts p ON bc.child_part_id = p.part_id
WHERE bc.parent_part_id = 1001 -- 成品 ID
AND bc.depth > 0
GROUP BY bc.child_part_id, p.name;
