SQL递归查询实现 SQL递归CTE完整教程

裘德小鎮的故事

发布时间：2025-07-04 19:10:02

577人浏览过

来源于php中文网

原创

sql递归查询通过递归cte实现，适合处理层级数据。1. 递归cte由初始查询、递归查询和终止条件组成；2. 使用with recursive定义，必须用union all连接初始与递归部分；3. 应用于组织架构、分类树等场景；4. 注意优化性能，如加索引、限深度；5. 不同数据库语法略有差异，需查阅文档。掌握递归cte可高效解决层级查询问题。

SQL递归查询实现 SQL递归CTE完整教程

SQL递归查询，简单来说，就是让SQL自己调用自己，一层层地往下查，直到满足某个条件为止。它特别适合处理具有层级关系的数据，比如组织架构、商品分类、父子关系等等。

SQL递归CTE（Common Table Expression，通用表表达式）是一种实现递归查询的常用方法，它允许你定义一个临时的、命名的结果集，然后在同一个查询中多次引用它，包括在自身的定义中。

SQL递归CTE完整教程

什么是递归CTE？

递归CTE本质上就是一个临时表，但它特别的地方在于，这个表可以在自己的定义中引用自己。这就像一个函数调用自身一样，每次调用都会产生新的结果，直到满足某个终止条件。

递归CTE的语法

递归CTE的基本语法如下：

WITH RECURSIVE cte_name AS (
    -- 初始查询（Anchor Member）
    SELECT ...
    UNION ALL
    -- 递归查询（Recursive Member）
    SELECT ...
    FROM cte_name
    WHERE ... -- 递归终止条件
)
-- 主查询
SELECT ...
FROM cte_name;

WITH RECURSIVE cte_name AS (...): 定义一个名为 cte_name 的递归CTE。RECURSIVE 关键字是必须的，告诉SQL引擎这是一个递归CTE。
初始查询（Anchor Member）: 这是递归的起点，它返回递归的第一层结果。通常，这个查询会选择根节点或者满足特定条件的初始记录。
UNION ALL: 将初始查询的结果和递归查询的结果合并在一起。注意，这里必须使用 UNION ALL，而不是 UNION，因为 UNION 会去重，而递归查询中可能存在重复数据。
递归查询（Recursive Member）: 这是递归的核心部分，它引用了自身 (cte_name)，并根据一定的条件进行查询。每次递归都会基于上一次的结果进行查询，直到满足终止条件。
WHERE ... (递归终止条件): 这是递归的终止条件，它决定了何时停止递归。如果没有终止条件，递归将会无限循环，最终导致错误。
主查询: 这是最终的查询，它从递归CTE (cte_name) 中选择所需的数据。

递归CTE示例：组织架构

假设我们有一个名为 employees 的表，用于存储员工信息，其中包含 employee_id (员工ID)、employee_name (员工姓名) 和 manager_id (经理ID) 字段。我们可以使用递归CTE来查询某个员工的所有下属：

WITH RECURSIVE subordinates AS (
    -- 初始查询：找到指定员工的直接下属
    SELECT employee_id, employee_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id = 1 -- 假设员工ID为1是CEO

    UNION ALL

    -- 递归查询：找到所有下属的下属
    SELECT e.employee_id, e.employee_name, e.manager_id, s.level + 1 AS level
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.employee_id
)
-- 主查询：选择所有下属的信息
SELECT employee_id, employee_name, level
FROM subordinates;

这个例子中，初始查询选择了CEO（manager_id = 1）的所有直接下属。递归查询则基于 subordinates 表，找到所有下属的下属，并将层级 level 加 1。递归会一直进行，直到没有更多的下属为止。

递归CTE的注意事项

终止条件： 确保你的递归CTE有明确的终止条件，否则会导致无限循环。
性能： 递归CTE可能会对性能产生影响，特别是当处理大量数据时。尽量优化查询，避免不必要的递归。
数据库支持： 并非所有数据库都支持递归CTE。常见的数据库如PostgreSQL、SQL Server、MySQL 8.0+、Oracle 11g Release 2+ 都支持。