PostgreSQL的WITH RECURSIVE提供了一种在单条SQL中反复引用自身结果集的查询能力。普通CTE在一次查询中只计算一次，而递归CTE会把每一轮迭代产生的新行继续作为下一轮输入，直到条件不再满足为止。在批量处理场景中，这种能力可以替代大量应用层循环，让数据库直接完成集合级别的递归运算，减少网络往返和中间状态维护。

一、递归CTE的执行模型与基础语法

递归CTE由两部分组成：非递归初始项和递归项，中间用UNION ALL连接。非递归项负责提供第一批行，这些行会放入内部工作表；随后数据库反复执行递归项，并引用当前工作表中的数据生成新行，再把新行合并到结果集和工作表，直到某一轮递归项返回空集。整个过程在单条SQL内完成，用户看到的是最终合并后的结果。

下面这个例子用递归CTE生成1到10的数字。初始项是SELECT 1，递归项每次把当前数字加1，并通过WHERE n < 10控制终止条件，避免无限递归。

WITH RECURSIVE nums(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 10
)
SELECT * FROM nums;

递归项中必须包含能够收紧范围的过滤条件，例如数字递增、日期递增、数量递减或深度递增。如果递归始终产生新行，PostgreSQL会一直执行下去，直到触发系统对递归查询的限制或事务超时。另外，递归CTE通常使用UNION ALL而不是UNION。UNION会进行去重和排序，不仅增加额外开销，还可能把递归产生的重复行合并掉，导致无法继续生成新的行，从而使递归提前终止。因此，除非业务上明确要求去重，否则都应保留UNION ALL。

二、用递归CTE批量生成数据

批量生成数据是递归CTE的常见用途之一。比如构造日期维度表、创建测试数据、生成连续编号，或者按指定数量拆分记录。与generate_series不同，递归CTE可以基于更复杂的业务规则逐步派生数据，例如跳过周末、根据库存策略递减数量、按父级属性继承某些字段。这样就不再需要把基础数据拉到应用层循环处理，再拼成多条INSERT语句。

下面的SQL生成本月从第一天到最后一天的所有日期。初始项通过date_trunc获得月初日期，递归项每次加一天，直到小于下个月一号为止。

WITH RECURSIVE days(d) AS (
    SELECT date_trunc('month', CURRENT_DATE)::date
    UNION ALL
    SELECT d + 1 FROM days WHERE d < (date_trunc('month', CURRENT_DATE) + interval '1 month - 1 day')::date
)
SELECT d FROM days;

另一个典型场景是按数量拆分记录。假设库存表里有一批数量为5的物料，需要拆成5条单位数量为1的明细，可以用递归CTE逐条递减剩余数量，直到剩余数量小于等于1。相比在应用中循环5次，数据库只执行一条SQL就能得到全部明细。

WITH RECURSIVE split(id, remain) AS (
    SELECT id, qty FROM inventory WHERE id = 100
    UNION ALL
    SELECT id, remain - 1 FROM split WHERE remain > 1
)
SELECT id, 1 AS unit_qty FROM split;

如果需要插入目标表，可以在递归CTE外层直接加INSERT语句。例如将生成的1000条编号写入任务表，只需把INSERT INTO target_table放在WITH前方，SELECT结果会被逐行插入。需要注意的是，大批量生成数据时递归深度会线性增长，如果一次性生成数十万行，单条SQL可能长时间占用锁和内存。此时可以结合业务分批提交，或者使用generate_series处理简单的等差序列，递归CTE更适合需要动态规则和业务判断的场景。

三、递归CTE处理树形结构与批量聚合

自引用表是递归CTE最经典的应用场景。部门表或分类表通常通过parent_id指向自身主键，如果要在一次查询中拿到某个节点全部下级、计算深度、生成完整路径，递归CTE可以轻松实现。非递归项取出根节点，递归项把子节点通过JOIN连接到当前结果上，每轮迭代自动向下展开一层。

WITH RECURSIVE dept_tree(id, parent_id, name, depth, path) AS (
    SELECT id, parent_id, name, 1, name::text
    FROM departments
    WHERE parent_id IS NULL
    UNION ALL
    SELECT d.id, d.parent_id, d.name, t.depth + 1, t.path || ' -> ' || d.name
    FROM departments d
    JOIN dept_tree t ON d.parent_id = t.id
)
SELECT id, name, depth, path FROM dept_tree ORDER BY path;

在这个例子中，path字段通过递归不断拼接父路径与子节点名称，方便后续排序和展示层级。depth字段表示节点在第几层。得到整棵树后，还可以继续在外层关联事实表，按根节点批量汇总业务数据。例如销售订单表里记录了各部门的成交金额，把递归生成的树与订单表做JOIN，再按根部门分组汇总，就能得到每个部门的完整子树销售总额。

WITH RECURSIVE tree(root_id, id) AS (
    SELECT id, id FROM departments WHERE parent_id IS NULL
    UNION ALL
    SELECT t.root_id, d.id
    FROM departments d
    JOIN tree t ON d.parent_id = t.id
)
SELECT t.root_id, sum(o.amount) AS subtree_total
FROM tree t
JOIN orders o ON o.dept_id = t.id
GROUP BY t.root_id;

树形递归的难点在于防止环路。如果表里由于历史数据错误或业务允许双向关联而存在环，递归查询会不断遍历同一批节点，直到资源耗尽。解决思路是在递归过程中维护一个已访问节点数组，每次只连接不在数组中的子节点。PostgreSQL数组类型可以直接在WHERE条件中使用ANY判断。

WITH RECURSIVE tree(id, parent_id, depth, path) AS (
    SELECT id, parent_id, 1, ARRAY[id]
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.parent_id, t.depth + 1, t.path || c.id
    FROM categories c
    JOIN tree t ON c.parent_id = t.id
    WHERE NOT c.id = ANY(t.path)
)
SELECT id, parent_id, depth, path FROM tree;

这种方式用path数组记录已经经过的节点ID，当子节点已经出现在路径中时跳过，避免环形引用。不过如果数据量很大，数组比较会增加每轮迭代的计算成本，应结合唯一约束和索引减少无效扫描。

四、递归CTE配合批量更新与性能考量

递归CTE不仅能查询，还能把计算结果直接写回原表。典型用法是先通过WITH RECURSIVE生成带层级深度或路径的结果集，再在外层执行UPDATE，把结果集与目标表按主键关联后更新字段。例如需要给分类表批量计算每个条目的层级深度，可以一次性更新全部记录，而不用逐条处理。

WITH RECURSIVE tree AS (
    SELECT id, parent_id, 1 AS depth
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.parent_id, t.depth + 1
    FROM categories c
    JOIN tree t ON c.parent_id = t.id
)
UPDATE categories c
SET depth = t.depth
FROM tree t
WHERE c.id = t.id;

性能方面，递归CTE的每一轮迭代都相当于一次小规模查询。如果递归深度较大，JOIN条件上的索引至关重要。通常需要在parent_id和主键id上建立索引，确保每次连接时能够快速定位子节点。对于深度特别大的层级，可以在递归项中加入最大深度限制，防止异常数据拖垮查询。下面在递归项里限制depth小于20，确保最多展开20层。

WITH RECURSIVE tree(id, parent_id, depth) AS (
    SELECT id, parent_id, 1
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.parent_id, t.depth + 1
    FROM categories c
    JOIN tree t ON c.parent_id = t.id
    WHERE t.depth < 20
)
SELECT * FROM tree;

除了索引，还要关注递归CTE的工作表内存占用。PostgreSQL在递归过程中维护内部工作表，如果每轮产生大量行且深度很高，可能占用较多内存或临时文件。可以通过EXPLAIN ANALYZE观察递归迭代次数和每轮返回行数，发现异常后调整WHERE条件、拆分查询或增加work_mem。对于特别复杂的批量处理，递归CTE可以先将结果物化到临时表，再分步执行更新，降低单条SQL的复杂度。

总的来说，PostgreSQL CTE递归为批量处理提供了一种声明式的集合运算思路，适合统一处理一组有递推关系的数据。设计递归SQL时，重点要明确初始行、递推规则和终止条件，并注意UNION ALL、索引和环路防护。只要控制好范围和深度，递归CTE可以让很多原本依赖循环和多次查询的批量任务变得简洁而高效。