递归查询是SQL中一种强大的功能，允许您查询具有层次结构或树形结构的数据。它通过公用表表达式(CTE)实现，特别是使用WITH RECURSIVE语法。
WITH RECURSIVE 是 SQL 中用于实现递归查询的强大特性，它允许你编写能够引用自身的查询，非常适合处理层次结构或图状数据。
基本工作原理
递归查询由三个关键部分组成：
-
非递归项（初始查询）：这是递归的起点，提供初始结果集
-
递归项：这部分引用正在定义的临时表自身
-
终止条件：当递归不再产生新行时停止
执行流程
递归查询的执行遵循以下步骤：
-
首先执行非递归部分，生成初始结果集（称为"锚成员"）
-
然后重复执行递归部分，每次使用前一次迭代的结果作为输入
-
当递归部分不再产生新行时，递归终止
-
将所有迭代的结果合并为最终结果
WITH RECURSIVE 递归表名 AS (-- 非递归部分（初始查询）SELECT 初始列 FROM 初始表 WHERE 初始条件UNION [ALL]-- 递归部分SELECT 递归列 FROM 递归表名 JOIN 其他表 ON 连接条件 WHERE 递归条件
)
SELECT * FROM 递归表名;常见应用场景
1. 组织层次结构（查找所有下属）
WITH RECURSIVE employee_hierarchy AS (-- 基础查询：选择顶级经理SELECT id, name, manager_id, 1 AS levelFROM employeesWHERE manager_id IS NULLUNION ALL-- 递归查询：选择下属SELECT e.id, e.name, e.manager_id, eh.level + 1FROM employees eJOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy ORDER BY level, id;2. 路径查找（查找所有路径）
WITH RECURSIVE path_finder AS (-- 基础查询：起点SELECT id, point_a, point_b, CAST(point_a AS VARCHAR) || '->' || point_b AS pathFROM routesWHERE point_a = 'A'UNION ALL-- 递归查询：扩展路径SELECT r.id, r.point_a, r.point_b, pf.path || '->' || r.point_bFROM routes rJOIN path_finder pf ON r.point_a = pf.point_bWHERE pf.path NOT LIKE '%' || r.point_b || '%' -- 避免循环
)
SELECT * FROM path_finder;3. 物料清单（BOM）展开
WITH RECURSIVE bom_expansion AS (-- 基础查询：顶级产品SELECT component_id, parent_id, quantity, 1 AS levelFROM bomWHERE parent_id = 'PRODUCT_X'UNION ALL-- 递归查询：子组件SELECT b.component_id, b.parent_id, b.quantity * be.quantity, be.level + 1FROM bom bJOIN bom_expansion be ON b.parent_id = be.component_id
)
SELECT * FROM bom_expansion ORDER BY level, component_id;注意事项
-
递归深度限制：大多数数据库有默认递归深度限制（如100或1000），可以通过配置调整
-
循环检测：需要确保数据没有循环引用，否则可能导致无限递归
-
性能：递归查询可能对大型层次结构性能较差，考虑使用物化路径或嵌套集等替代方案
