sql中with怎么使用 WITH临时表达式的2种递归写法-SQL-PHP中文网

sql中with怎么使用 WITH临时表达式的2种递归写法

裘德小鎮的故事

发布： 2025-07-06 14:31:18

原创

530人浏览过

递归with表达式用于处理层级结构数据，有两种写法。一是基本递归，包含锚定成员和递归成员，适用于单根层级结构；二是多锚点递归，包含多个锚定成员，适用于多根层级结构。优化技巧包括限制递归深度、使用索引、避免不必要的计算、使用物化视图。应用场景有网络拓扑分析、社交网络分析、权限管理和供应链管理。与临时表相比，with表达式作用域和生命周期更短，性能更好，语法更简洁。选择依据是中间结果的使用范围和存储需求。

sql中with怎么使用 WITH临时表达式的2种递归写法

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 将零件与其组成部分关联起来。这种写法适用于那些有多个起始点的层级结构。