如何用SQLite递归CTE遍历含嵌入树的树形结构

要展开嵌入树的完整结构，需要在递归CTE中新增分支处理EmbeddedTree关联的子节点，以下是修正后的SQL语句：

WITH RECURSIVE
under_root(name, level, embeddedref, parent) AS (
    -- 起始节点：选中Airplane
    SELECT ElementName, 0, EmbeddedTree, Parent
    FROM ELEMENTS
    WHERE ElementName = 'Airplane'
    
    UNION ALL
    
    -- 分支1：遍历当前节点的直接子节点（常规父-子关系）
    SELECT e.ElementName, ur.level + 1, e.EmbeddedTree, e.Parent
    FROM ELEMENTS e
    JOIN under_root ur ON e.Parent = ur.name
    
    UNION ALL
    
    -- 分支2：遍历当前节点嵌入树的所有子节点
    SELECT e.ElementName, ur.level + 1, e.EmbeddedTree, e.Parent
    FROM ELEMENTS e
    JOIN under_root ur ON e.Parent = ur.embeddedref
    WHERE ur.embeddedref IS NOT NULL
)
SELECT name, level, embeddedref, parent
FROM under_root
ORDER BY level, name;

逻辑说明 #

• 初始查询直接定位Airplane节点作为递归起点。
• 第一个UNION ALL分支保持原有逻辑，处理常规的父节点-子节点遍历，比如获取Airplane的直接子节点Airplane.EngineLeft和Airplane.EngineRight。
• 第二个UNION ALL分支专门处理嵌入树展开：当当前节点存在EmbeddedTree引用时（比如Airplane.EngineLeft的embeddedref为Engine），自动拉取所有以该引用为父节点的子节点（即Engine.Temperature），并继承当前节点的层级+1作为新层级。如果嵌入的子节点自身还有EmbeddedTree，也会继续递归展开。

执行该语句后，将得到你需要的包含嵌入树完整结构的结果：

| name                 | level | embeddedref | parent   |
|----------------------|-------|-------------|----------|
| Airplane             | 0     | NULL        | NULL     |
| Airplane.EngineLeft  | 1     | Engine      | Airplane |
| Airplane.EngineRight | 1     | Engine      | Airplane |
| Engine.Temperature   | 2     | NULL        | Engine   |
| Engine.Temperature   | 2     | NULL        | Engine   |

内容的提问来源于stack exchange，提问作者Rene Lorenz