如何用MySQL递归CTE获取指定树形节点下的所有关联Item？

你的原递归CTE存在两个问题：一是关联条件写错了表名（spaces是不存在的，应该用tree），二是仅查询了树形结构节点，没有关联items表获取对应的条目。下面是修正后的完整解决方案：

正确的SQL查询语句 #

WITH RECURSIVE tree_hierarchy AS (
    -- 锚点：选择起始节点
    SELECT id
    FROM tree
    WHERE id = 1 -- 替换为你要查询的起始Tree节点ID
    UNION ALL
    -- 递归：获取所有子节点
    SELECT tree.id
    FROM tree
    INNER JOIN tree_hierarchy ON tree.parent = tree_hierarchy.id
)
-- 关联items表，取出所有关联的条目
SELECT items.*
FROM items
INNER JOIN tree_hierarchy ON items.tree_id = tree_hierarchy.id;

代码说明 #

1. 递归CTE部分：

   • 锚点查询：指定起始节点ID，获取该节点的ID
   • 递归查询：通过tree.parent = tree_hierarchy.id关联，不断获取当前节点的所有子节点，直到遍历完整个子树

2. 关联Items表：

   • 用递归得到的所有节点ID集合，关联items表的tree_id字段，即可取出该节点及其所有子节点关联的全部Items

不同起始节点的测试示例 #

• 起始节点ID为1（对应Tree-1）：返回Item-1、Item-2、Item-3、Item-4
• 起始节点ID为2（对应Tree-2）：返回Item-3、Item-4
• 起始节点ID为3（对应Tree-3）：返回Item-4

原代码的错误修正点 #

• 把INNER JOIN tree_hierarchy ON spaces.parent = tree_hierarchy.id改为INNER JOIN tree_hierarchy ON tree.parent = tree_hierarchy.id，不存在spaces表，应引用tree表的parent字段
• 去掉了不需要的name和parent字段，仅保留id即可（关联Items只需要节点ID）

内容的提问来源于stack exchange，提问作者Mark Tyers