SQL递归获取完整的树形结构数据

在 SQL 中，WITH RECURSIVE 用于创建递归查询，它允许在查询中引用自身。这种查询通常用于处理具有层次结构的数据，例如树形结构。

以下是使用 WITH RECURSIVE 创建递归查询的一般语法：

WITH RECURSIVE [alias] ([column1], [column2], ...) AS
(
    -- 初始查询部分
    SELECT [column1], [column2], ...
    FROM [table]
    WHERE [condition]

    UNION ALL

    -- 递归查询部分
    SELECT [column1], [column2], ...
    FROM [alias]
    WHERE [condition]
)
SELECT * FROM [alias];

在上面的语法中，你需要提供以下信息：

• alias：递归查询的别名，用于在递归查询部分引用自身。
• [column1], [column2], ...：要选择的列。
• [table]：包含数据的表名。
• [condition]：用于限制初始查询和递归查询的条件的谓词。

以下是一个使用 WITH RECURSIVE 查询的示例，它从顶层节点开始，递归地获取整棵树的数据：

WITH RECURSIVE tree (id, name, parent_id) AS
(
    SELECT id, name, parent_id
    FROM categories
    WHERE parent_id IS NULL -- 获取顶层节点
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    INNER JOIN tree t ON c.parent_id = t.id -- 递归获取子节点
)
SELECT * FROM tree;

在上面的示例中，categories 是包含分类信息的表，每个分类都有一个唯一的 id 和一个指向父分类的 parent_id。通过使用 WITH RECURSIVE，我们可以从顶层节点开始，递归地获取整棵树的分类信息。初始查询部分选择了顶层节点的数据，然后通过递归查询部分逐级获取子节点。最后，通过 SELECT * FROM tree 获取完整的树形结构数据。