引言：认识WITH临时表达式

在SQL开发中，我们经常会遇到需要多次引用同一子查询结果的场景。传统的做法是重复编写相同的子查询或者创建临时表，但这两种方式都存在明显缺陷：前者导致代码冗长且难以维护，后者则需要额外的存储资源。SQL标准的WITH子句（又称Common Table Expression，CTE）正是为解决这类问题而生。

作为一名长期与数据库打交道的开发者，我发现WITH子句特别是其递归功能，在处理层次化数据时展现出惊人的威力。今天，我将分享两种不同的递归写法，并通过实际案例演示它们的应用差异。

递归CTE的基本结构

递归CTE允许我们引用自身的定义，这在处理树形结构或图数据时特别有用。其基本语法如下：

sql
WITH RECURSIVE cte_name AS (
-- 基础查询（非递归部分）
SELECT columns FROM table WHERE condition

UNION [ALL]

-- 递归部分
SELECT columns FROM cte_name JOIN table ON condition

)
SELECT * FROM cte_name;

关键点在于UNION（或UNION ALL）连接的两个部分：第一个是基础查询，提供递归的起点；第二个是递归部分，可以引用CTE自身。

第一种写法：自底向上递归

这种写法从叶子节点开始，逐步向上追溯父节点。让我们以员工-经理层次结构为例：

sql
WITH RECURSIVE employeehierarchy AS ( -- 基础部分：找出所有非管理岗员工（叶子节点） SELECT employeeid, name, managerid, 1 AS level FROM employees WHERE employeeid NOT IN (SELECT managerid FROM employees WHERE managerid IS NOT NULL)

UNION ALL

-- 递归部分：向上查找每个员工的经理
SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.employee_id = eh.manager_id

)
SELECT * FROM employeehierarchy ORDER BY level, employeeid;

这种写法特别适合需要从具体细节开始，逐步汇总到高层级的分析场景。我曾在一个组织架构分析项目中使用这种方法，有效识别出管理链条中的瓶颈节点。

实际应用中的注意事项：
1. 确保递归有终止条件，避免无限循环
2. 对于大型层次结构，考虑设置递归深度限制
3. 注意UNION和UNION ALL的选择：前者去重但性能较低，后者保留所有记录

第二种写法：自顶向下递归

与第一种相反，这种写法从根节点开始，逐步展开子节点。以产品分类为例：

sql
WITH RECURSIVE categorytree AS ( -- 基础部分：找出所有顶级分类 SELECT categoryid, name, parentid, CAST(name AS VARCHAR(1000)) AS path FROM categories WHERE parentid IS NULL

UNION ALL

-- 递归部分：向下查找每个分类的子分类
SELECT c.category_id, c.name, c.parent_id, 
       CONCAT(ct.path, ' > ', c.name) AS path
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.category_id

)
SELECT * FROM category_tree ORDER BY path;

这种写法生成的可读性路径（path）特别有用。在一个电商平台项目中，我用这种方法实现了面包屑导航的动态生成。

性能优化技巧：
1. 为parent_id等连接字段建立索引
2. 对于已知深度的层次，可以在基础查询中预过滤
3. 考虑使用MATERIALIZED提示（如果数据库支持）来缓存中间结果

两种写法的对比分析

| 特性 | 自底向上递归 | 自顶向下递归 |
|---------------|--------------------------|--------------------------|
| 起始点 | 叶子节点 | 根节点 |
| 适用场景 | 汇总分析 | 展开浏览 |
| 典型应用 | 计算子节点数量 | 生成完整路径 |
| 性能考虑 | 通常需要处理更多中间结果 | 通常能较早过滤无用分支 |
| 输出顺序 | 从细节到汇总 | 从整体到细节 |

在最近的一个社交网络分析项目中，我同时使用了这两种方法：自顶向下查找用户的影响范围，自底向上分析信息传播路径，两者结合提供了全面的视图。

高级递归技巧

1. 循环检测：在处理可能有循环的图结构时，需要特别处理

sql
WITH RECURSIVE graphpath AS ( SELECT nodeid, nextnodeid, ARRAY[nodeid] AS path, FALSE AS cycle FROM graphedges
WHERE nodeid = startnode

UNION ALL

SELECT e.node_id, e.next_node_id, gp.path || e.node_id,
       e.node_id = ANY(gp.path) AS cycle
FROM graph_edges e
JOIN graph_path gp ON e.node_id = gp.next_node_id
WHERE NOT gp.cycle

)
SELECT * FROM graph_path;

1. 聚合递归：在递归过程中进行累加计算

sql
WITH RECURSIVE financialimpact AS ( SELECT departmentid, cost, departmentid AS rootdepartment
FROM departments
WHERE parent_id IS NULL

UNION ALL

SELECT d.department_id, fi.cost + d.cost, fi.root_department
FROM departments d
JOIN financial_impact fi ON d.parent_id = fi.department_id

)
SELECT rootdepartment, SUM(cost) AS totalcost
FROM financialimpact GROUP BY rootdepartment;

1. 递归限制：大多数数据库支持设置递归深度上限

sql -- PostgreSQL示例 WITH RECURSIVE limited_recursion AS ( SELECT ... UNION ALL SELECT ... FROM limited_recursion WHERE ... ) SELECT * FROM limited_recursion OPTION (MAXRECURSION 100);

常见问题与解决方案

Q：递归CTE性能差怎么办？
A：1) 确保连接字段有索引 2) 尽量在基础查询中过滤更多数据 3) 考虑使用临时表分阶段处理

Q：如何避免无限循环？
A：1) 确保数据没有循环引用 2) 设置递归深度限制 3) 添加循环检测逻辑

Q：为什么我的递归CTE结果不全？
A：检查UNION和UNION ALL的使用是否正确，前者会去重可能导致记录丢失

结语

递归CTE是SQL中极其强大的工具，掌握这两种基本写法后，你会发现许多原本复杂的层次化数据处理变得简单明了。在我的开发生涯中，递归CTE曾帮助我解决了组织架构分析、产品分类管理、社交关系追踪等多个领域的难题。