递归CTE：这是什么，有啥用？

今天我们再进阶一步，来点递归魔法。如果你以前用过支持递归的编程语言（比如Python），大概知道是怎么回事。但别慌，就算听起来有点玄乎，我们也会讲得很细。

递归CTE是处理层级、树状数据结构的神器，比如公司组织架构、家谱或者文件目录啥的。

简单说，就是这种表达式能“自己调用自己”，一步步遍历和处理所有数据层级。

递归CTE的关键点：

1. 它们用关键字WITH RECURSIVE。
2. 递归CTE分两部分：
   • 基础查询：定义递归的起点（或者说“根”）。
   • 递归查询：用上一步的结果处理剩下的数据。

递归CTE的算法就像你爬楼梯：

• 先站上第一阶（基础查询）。
• 再踩第二阶，用第一阶的结果（递归查询）。
• 这个过程一直重复，直到没楼梯了（达到终止条件）。

递归CTE的语法

直接上个模板例子：

WITH RECURSIVE cte_name AS (
    -- 基础查询
    SELECT column1, column2
    FROM table_name
    WHERE condition_for_base_case

    UNION ALL

    -- 递归查询
    SELECT column1, column2
    FROM table_name
    JOIN cte_name ON some_condition
    WHERE stop_condition
)
SELECT * FROM cte_name;

UNION和UNION ALL在递归CTE里的作用

每个递归CTE必须在基础部分和递归部分之间用UNION或者UNION ALL。

选哪个：UNION还是UNION ALL？

如果你不确定用哪个——几乎总是选UNION ALL。为啥？因为它更快：直接合并结果，不管有没有重复。也就是说——更少计算、更省资源、更快出结果。

尤其是在递归CTE里。比如你要构建树形评论、公司下属结构——UNION ALL几乎是标配。如果用UNION，数据库可能会误以为某些步骤已经有了，结果“砍掉”一部分数据，整个遍历逻辑就崩了。

只有你确定重复数据有害、必须去掉时，才用UNION。但记住：这总是在“干净”和“速度”之间做权衡。

不同写法的例子

-- UNION: 重复的会被去掉
SELECT 'A'
UNION
SELECT 'A';     -- 结果：一行 'A'

-- UNION ALL: 重复的也保留
SELECT 'A'
UNION ALL
SELECT 'A';     -- 结果：两行 'A'

递归查询里，最好一直用UNION ALL，这样遍历结构时不会漏掉重要步骤。

来看个常见需求：有个员工表，字段有employee_id、manager_id和name。要从老板（manager_id = NULL）开始，构建层级结构。

假设有员工表：employees

我们要搞清楚谁归谁管，还要知道每个员工在结构里的层级。这很方便，比如你想在界面上画员工树，或者做团队结构报告。

WITH RECURSIVE employee_hierarchy AS (
    -- 从没有上司的人开始
    SELECT
        employee_id,
        name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 加入下属，层级+1
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh
    ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

结果会是这样：

这个查询很直观地展示了怎么“走遍”员工层级——从老板到最底层。level字段很适合用来格式化或可视化树。

例子：商品分类

现在假设我们有商品分类表，每个分类可以有子分类，子分类还可以有自己的子分类。怎么构建分类树？

表categories

递归查询：

WITH RECURSIVE category_tree AS (
    -- 基础情况：找根分类
    SELECT
        category_id,
        name,
        parent_id,
        1 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归部分：找当前分类的子分类
    SELECT
        c.category_id,
        c.name,
        c.parent_id,
        ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_id = ct.category_id
)
SELECT * FROM category_tree;

结果：

现在我们能看到带层级的分类树了。

为啥递归CTE这么牛？

递归CTE是SQL里最强大、最优雅的工具之一。你不用写复杂的嵌套逻辑，只要描述起点（基础情况）和怎么往下走（递归部分）——剩下的交给PostgreSQL。

最常见的用法就是遍历层级结构：员工、商品分类、磁盘目录、社交网络图啥的。它们很容易扩展：表里加了新数据，查询自动能查出来。又方便又能扩容。

但也有坑。一定要注意终止条件——不然查询会死循环。别忘了加索引：大表里递归查询没索引会很慢。还有UNION ALL——几乎总是最优选，特别是层级结构，不然去重会让你丢掉递归步骤。

调教好的递归CTE能把复杂业务逻辑用几行SQL就搞定——不用写过程、循环、额外代码。这就是SQL又对又美的时刻。

递归CTE常见错误

• 死循环：如果你没写好终止条件（WHERE），查询会一直转下去。
• 数据太多：UNION ALL用错会加一堆重复。
• 性能问题：递归查询数据量大时很吃资源。关键字段（比如manager_id）加索引能提速。

哪些场景离不开递归查询

有时候觉得递归查询只是理论，但其实日常开发经常用到。比如：

• 做公司结构或商品分类的报表；
• 遍历文件夹树，拿到所有子目录列表；
• 分析图结构——社交关系、路径、任务依赖啥的；
• 把复杂对象关系用可读方式展示出来。

只要你要遍历“一个依赖另一个”的结构——基本都能用WITH RECURSIVE搞定。