SQL 递归查询详解

SQL递归查询是一种强大的工具，允许你在数据库中处理层次结构或树形数据。通过递归查询，你可以轻松地遍历父子关系、组织结构图、文件目录等复杂数据结构。本文将详细介绍SQL递归查询的概念、语法及其实际应用。

什么是递归查询？

递归查询是一种特殊的SQL查询，它允许你在查询中引用自身。这种查询通常用于处理具有层次结构的数据，例如组织结构、文件系统或分类树。递归查询通过不断迭代，直到满足某个终止条件为止。

在SQL中，递归查询通常通过WITH RECURSIVE语句实现。它由两部分组成：

基础查询（Base Query）：这是递归的起点，返回初始结果集。
递归查询（Recursive Query）：这部分引用自身，并基于前一次迭代的结果生成新的结果集。

递归查询的基本语法

以下是递归查询的基本语法：

WITH RECURSIVE cte_name AS (
    -- 基础查询
    SELECT ...
    FROM ...
    WHERE ...

    UNION ALL

    -- 递归查询
    SELECT ...
    FROM cte_name
    WHERE ...
)
SELECT * FROM cte_name;

cte_name：这是递归公共表表达式（CTE）的名称。
基础查询：返回初始结果集。
递归查询：引用cte_name并生成新的结果集，直到满足终止条件。
UNION ALL：将基础查询和递归查询的结果合并。

备注

递归查询必须包含一个终止条件，否则会导致无限循环。

递归查询的示例

示例1：遍历组织结构

假设我们有一个表示组织结构的表employees，其中包含员工的ID、姓名和经理ID（即父节点）：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO employees (id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 3);

现在，我们希望查询Alice的所有下属（包括直接和间接下属）。可以使用递归查询实现：

WITH RECURSIVE subordinates AS (
    -- 基础查询：找到Alice的直接下属
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id = 1

    UNION ALL

    -- 递归查询：找到下属的下属
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

输出结果：

id	name	manager_id
2	Bob	1
3	Charlie	1
4	David	2
5	Eve	3

示例2：计算阶乘

递归查询不仅可以用于层次结构数据，还可以用于数学计算。例如，计算5的阶乘：

WITH RECURSIVE factorial AS (
    -- 基础查询：初始值为1
    SELECT 1 AS n, 1 AS result

    UNION ALL

    -- 递归查询：逐步计算阶乘
    SELECT n + 1, result * (n + 1)
    FROM factorial
    WHERE n < 5
)
SELECT result FROM factorial WHERE n = 5;

输出结果：

result
120

递归查询的实际应用场景

组织结构图：查询某个员工的所有下属或上级。
文件系统：遍历文件夹及其子文件夹中的所有文件。
分类树：查询某个分类及其所有子分类。
路径查找：在图中查找从一个节点到另一个节点的路径。

总结

SQL递归查询是一种强大的工具，特别适合处理层次结构或树形数据。通过WITH RECURSIVE语句，你可以轻松地遍历复杂的数据结构。本文通过示例和逐步讲解，帮助你理解递归查询的基本概念和语法。

提示

练习是掌握递归查询的最佳方式。尝试在数据库中创建自己的层次结构数据，并编写递归查询来遍历它们。

附加资源

希望本文能帮助你掌握SQL递归查询的核心知识！如果你有任何问题，欢迎在评论区留言。

什么是递归查询？​

递归查询的基本语法​

递归查询的示例​

示例1：遍历组织结构​

示例2：计算阶乘​

递归查询的实际应用场景​

总结​

附加资源​