| 引擎 | PostgreSQL/Hive/Spark 均支持 WITH RECURSIVE |
|---|---|
| 用途 | 展开层级、找根、找环 |
| 注意 | 深度与环保护 |
展开组织树
展开组织树
WITH RECURSIVE tree AS (
SELECT org_id, parent_id, 1 AS lvl, CAST(org_id AS string) AS path
FROM dim_org WHERE parent_id IS NULL
UNION ALL
SELECT o.org_id, o.parent_id, t.lvl + 1, concat(t.path, '/', o.org_id)
FROM dim_org o JOIN tree t ON o.parent_id = t.org_id
)
SELECT * FROM tree;层级规模大时物化路径列或闭包表，避免每次查询都递归。