递归with表达式用于处理层级结构数据，有两种写法。一是基本递归，包含锚定成员和递归成员，适用于单根层级结构；二是多锚点递归，包含多个锚定成员，适用于多根层级结构。优化技巧包括限制递归深度、使用索引、避免不必要的计算、使用物化视图。应用场景有网络拓扑分析、社交网络分析、权限管理和供应链管理。与临时表相比，with表达式作用域和生命周期更短，性能更好，语法更简洁。选择依据是中间结果的使用范围和存储需求。
WITH 表达式，说白了，就是SQL里的“临时表”。但它比临时表更灵活，也更强大。它能让你在查询中定义临时的、命名的结果集，然后在主查询中引用它们。这让复杂的SQL变得更易读、更易维护。而递归 WITH，则是解决层级结构数据的利器。
WITH 表达式主要有两种用法：非递归和递归。这里重点说说递归，因为它更考验理解。
递归 WITH 表达式的两种写法
递归 WITH 表达式的核心在于：它允许一个 WITH 子句引用它自身。这就像一个函数调用自身一样，可以用来处理层级数据，比如组织结构、树形菜单、供应链等等。
写法一：基本递归
这种写法是最常见的，也比较容易理解。它通常包含两个部分：
- 锚定成员 (Anchor Member): 这是递归的起点，它定义了递归的初始结果集。它就像树的根节点。
- 递归成员 (Recursive Member): 这是递归的部分，它引用 WITH 子句自身，并定义了如何从上一次迭代的结果集中生成新的结果集。它就像树的枝干。
WITH RECURSIVE employee_hierarchy AS (
-- 锚定成员：找到所有顶级员工（没有上级）
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 递归成员：找到所有下级员工
SELECT e.id, e.name, e.manager_id, eh.level + 1 AS level
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;这段代码会查询一个名为 employees 的表，并构建一个员工层级结构。锚定成员找到所有没有上级（manager_id IS NULL）的员工，作为层级的起点。递归成员则通过 JOIN 将每个员工与其上级关联起来，并递增层级 level。
写法二：多锚点递归
有时候，你的层级结构可能不是单根的，而是有多个根节点。这时候，就需要使用多锚点递归。
WITH RECURSIVE parts_explosion AS (
-- 锚定成员1：找到所有最终产品（没有零件组成它们）
SELECT part_id, part_name, 1 AS level
FROM parts
WHERE is_final_product = TRUE
UNION ALL
-- 锚定成员2：找到所有原材料（没有子零件）
SELECT part_id, part_name, 1 AS level
FROM parts
WHERE is_raw_material = TRUE
UNION ALL
-- 递归成员：找到所有零件的组成部分
SELECT p.part_id, p.part_name, pe.level + 1 AS level
FROM parts p
JOIN parts_explosion pe ON p.parent_part_id = pe.part_id
)
SELECT * FROM parts_explosion;在这个例子中，我们假设有一个 parts 表，其中包含零件的信息，以及零件之间的组成关系 (parent_part_id)。 这个例子有两个锚定成员：最终产品和原材料。递归成员则通过 parent_part_id 将零件与其组成部分关联起来。 这种写法适用于那些有多个起始点的层级结构。
如何优化 WITH 递归表达式的性能？
递归查询通常性能较差，特别是当数据量很大或者层级很深时。以下是一些优化技巧：
-
限制递归深度: 使用
LIMIT或者WHERE子句来限制递归的深度，避免无限递归。 -
使用索引: 确保在连接列（比如
manager_id或者parent_part_id）上创建了索引。 - 避免不必要的计算: 在递归成员中，尽量减少不必要的计算，只计算需要的信息。
- 使用物化视图: 如果递归查询的结果经常被使用，可以考虑创建一个物化视图来缓存结果。
WITH 表达式在实际项目中的应用场景有哪些？
除了上面提到的组织结构和零件组成，WITH 表达式还可以用于：
- 网络拓扑分析: 查找网络中两个节点之间的所有路径。
- 社交网络分析: 查找用户之间的共同好友。
- 权限管理: 查找用户拥有的所有权限，包括直接权限和继承权限。
- 供应链管理: 跟踪产品的整个生产流程。
WITH 表达式和临时表有什么区别？
虽然 WITH 表达式和临时表都可以用来存储中间结果，但它们之间还是有一些区别：
- 作用域: WITH 表达式的作用域仅限于当前查询，而临时表可以在多个查询中使用。
- 生命周期: WITH 表达式的生命周期仅限于当前查询的执行时间，而临时表可以在会话期间存在。
- 性能: WITH 表达式通常比临时表更快，因为它可以被优化器更好地优化。
- 语法: WITH 表达式的语法更简洁，更易读。
选择使用 WITH 表达式还是临时表，取决于你的具体需求。如果只需要在单个查询中使用中间结果，并且希望获得更好的性能，那么 WITH 表达式是更好的选择。如果需要在多个查询中使用中间结果，或者需要长期存储中间结果，那么临时表是更好的选择。
