多表递归查询问题：提取Table1条目完整父系层级及属性

需求说明 #

需要从Table1和Table2中提取Table1每条记录的自身属性，以及完整的父系层级路径（包含Location、Table2的父系链、Table1自身）。

示例表结构 #

-- Table1
| id | Name     | ParentIDFromTable2 | Weight | Height |
| 1  | Jack     | 5                  | 180    | 183    |
| 2  | Sparrow  | 3                  | 210    | 169    |
| 3  | John     | 6                  | 350    | 210    |
| 4  | Jill     | 2                  | 110    | 140    |
| 5  | Juliet   | 7                  | 122    | 150    |

-- Table2
| id | Name     | GrandParentID     | Location |
| 1  | Adam     | 0                 | Earth    |
| 2  | Zack     | 1                 | Earth    |
| 3  | Noah     | 2                 | Earth    |
| 4  | Jacob    | 3                 | Earth    |
| 5  | Jeff     | 4                 | Earth    |
| 6  | Drake    | 5                 | Earth    |
| 7  | Kanye    | 3                 | Earth    |

期望输出示例 #

• ID=1 >>> Name=Jack, Weight=180, Height=183, Lineage= Earth/Adam/Zack/Noah/Jacob/Jeff/Jack
• ID=5 >>> Name=Juliet, Weight=122, Height=150, Lineage= Earth/Adam/Zack/Noah/Kanye/Juliet

遇到的问题 #

尝试使用WITH RECURSIVE递归查询，但无法正确基于Table2的父ID（GrandParentID）实现递归逻辑，生成完整的父系链。

解决方案 #

可以通过两步递归实现：先递归遍历Table2生成完整的父系路径（包含Location），再关联Table1的记录，拼接自身信息得到最终的Lineage。

以下是完整的SQL查询（以PostgreSQL为例，其他支持递归CTE的数据库可稍作调整）：

WITH RECURSIVE table2_lineage AS (
    -- 递归基础项：Table2中根节点（GrandParentID=0）的记录
    SELECT 
        id,
        Name,
        Location,
        GrandParentID,
        CONCAT(Location, '/', Name) AS lineage_path
    FROM Table2
    WHERE GrandParentID = 0

    UNION ALL

    -- 递归项：关联父节点，拼接路径
    SELECT 
        t2.id,
        t2.Name,
        t2.Location,
        t2.GrandParentID,
        CONCAT(tl.lineage_path, '/', t2.Name) AS lineage_path
    FROM Table2 t2
    JOIN table2_lineage tl ON t2.GrandParentID = tl.id
),
final_result AS (
    SELECT 
        t1.id,
        t1.Name,
        t1.Weight,
        t1.Height,
        CONCAT(tl.lineage_path, '/', t1.Name) AS Lineage
    FROM Table1 t1
    JOIN table2_lineage tl ON t1.ParentIDFromTable2 = tl.id
)
SELECT * FROM final_result;

逻辑说明 #

• table2_lineage递归CTE：
  • 基础部分定位Table2的根节点（GrandParentID=0），生成初始路径Location/Name。
  • 递归部分通过GrandParentID关联父节点的递归结果，将父节点路径与当前节点Name拼接，逐步构建完整父系链。
• final_result关联查询：将Table1记录与递归得到的Table2路径关联，最后拼接Table1自身的Name，得到包含自身的完整Lineage。

执行上述查询后，即可得到符合期望的输出结果。

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