如何编写CTE语句遍历通过桥接表构建的树形结构

别慌！完全没接触过CTE也没关系，我结合你的items表+linker桥接表的场景，一步步给你讲明白怎么用CTE搞定树形结构遍历～

先给你快速扫个盲：CTE（Common Table Expression） 就是一种临时的结果集，你可以把它当成一个临时表来用，最关键的是它支持递归——这正是我们遍历多层树形关系的核心！递归CTE分为两部分：

• 锚点成员：就是树形结构的起点（比如根节点、目标子节点）
• 递归成员：就是不断和桥接表关联，一层层找到子节点/父节点的部分

先明确表结构假设 #

我先假设你的两张表结构大概是这样的（如果和实际有出入，你对应调整字段名就行）：

• items表：存储物品信息，至少有id（唯一ID）、name（物品名称）字段
• linker表：存储父子关系，有parent_id（父节点ID）、child_id（子节点ID）字段

示例1：从根节点向下遍历所有子孙节点 #

比如你想找id=1的根节点下面所有的子节点（包括多级嵌套的），可以用下面的递归CTE：

WITH RECURSIVE item_hierarchy AS (
    -- 锚点成员：先拿到根节点本身，作为遍历的起点
    SELECT 
        i.id,
        i.name,
        1 AS level  -- 标记层级，根节点是第1层
    FROM items i
    WHERE i.id = 1  -- 这里替换成你的目标根节点ID
    
    UNION ALL
    
    -- 递归成员：通过linker表，一层层找子节点
    SELECT 
        i.id,
        i.name,
        ih.level + 1 AS level
    FROM items i
    -- 从linker表找到当前节点对应的子节点ID
    JOIN linker l ON i.id = l.child_id
    -- 和递归结果集关联，把已找到的节点作为父节点，找它的子节点
    JOIN item_hierarchy ih ON l.parent_id = ih.id
)
-- 最后查询整个递归出来的层级结构
SELECT * FROM item_hierarchy;

这段代码的逻辑： #

1. 锚点成员先把根节点选出来，同时加个level字段方便你看当前节点在树形结构里的层级
2. 递归成员会不断执行：用linker表把已经找到的节点（item_hierarchy里的记录）作为父节点，找到它们对应的子节点，并且层级加1
3. 直到找不到更多子节点，递归就会停止，最后查询item_hierarchy就能得到完整的子孙节点树

示例2：从子节点向上遍历所有祖先节点 #

如果需要反过来，找某个子节点的所有父节点（比如找id=5的节点的所有上级节点），只需要调整递归关联的逻辑：

WITH RECURSIVE item_ancestors AS (
    -- 锚点成员：先拿到目标子节点本身
    SELECT 
        i.id,
        i.name,
        1 AS level
    FROM items i
    WHERE i.id = 5  -- 替换成你要找的子节点ID
    
    UNION ALL
    
    -- 递归成员：通过linker表找父节点
    SELECT 
        i.id,
        i.name,
        ia.level + 1 AS level
    FROM items i
    -- 从linker表找到当前节点对应的父节点ID
    JOIN linker l ON i.id = l.parent_id
    -- 和递归结果集关联，把已找到的节点作为子节点，找它的父节点
    JOIN item_ancestors ia ON l.child_id = ia.id
)
SELECT * FROM item_ancestors;

额外注意：避免循环递归 #

如果你的树形结构可能出现循环（比如父节点指向子节点，子节点又反过来指向父节点），可以加个path字段记录节点路径，防止无限递归：

WITH RECURSIVE item_hierarchy AS (
    SELECT 
        i.id,
        i.name,
        1 AS level,
        CAST(i.id AS VARCHAR(1000)) AS path  -- 记录路径，比如"1"
    FROM items i
    WHERE i.id = 1
    
    UNION ALL
    
    SELECT 
        i.id,
        i.name,
        ih.level + 1,
        CONCAT(ih.path, '->', i.id) AS path  -- 拼接路径，比如"1->3->5"
    FROM items i
    JOIN linker l ON i.id = l.child_id
    JOIN item_hierarchy ih ON l.parent_id = ih.id
    -- 检查路径里有没有当前节点，避免循环
    WHERE NOT ih.path LIKE CONCAT('%->', i.id, '%')
)
SELECT * FROM item_hierarchy;

内容的提问来源于stack exchange，提问作者Sean O