如何在Databricks SQL中递归获取最终CoId并关联数据？

在Databricks SQL中，**递归CTE（Common Table Expression）**是处理这种多层递归遍历场景的最佳方案，能动态覆盖任意层级的CoId嵌套。以下是具体实现步骤和代码：

前提字段假设 #

基于需求明确核心字段（实际结构不同时，仅需调整字段名）：

• Table1：包含ID, Type1, CoId, Type2字段
• Table2：包含CoId, Value字段
• Table3：需求中未涉及，暂不处理

实现代码 #

WITH RECURSIVE recursive_coid AS (
    -- 锚点成员：筛选Table1中Type1='13'的记录，作为递归起点
    SELECT
        ID,
        CoId AS current_coid,
        Type2
    FROM Table1
    WHERE Type1 = '13'
    
    UNION ALL
    
    -- 递归成员：继续查找下一层CoId，仅当当前节点满足递归条件时继续
    SELECT
        rc.ID,
        t1.CoId AS current_coid,
        t1.Type2
    FROM recursive_coid rc
    JOIN Table1 t1 ON rc.current_coid = t1.ID
    WHERE rc.Type2 = '13' AND rc.current_coid != 0
)
-- 最终结果：取每个ID的终止节点CoId，关联Table2获取Value
SELECT
    rc.ID,
    rc.current_coid AS `Final CoId`,
    t2.Value
FROM (
    -- 分组取每个ID的最后一条终止记录（递归层级最深的节点）
    SELECT
        ID,
        current_coid,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL) DESC) AS rn
    FROM recursive_coid
    -- 过滤停止条件：CoId为0 或 Type2不等于'13'
    WHERE current_coid = 0 OR Type2 != '13'
) rc
LEFT JOIN Table2 t2 ON rc.current_coid = t2.CoId
WHERE rc.rn = 1;

逻辑说明 #

1. 锚点成员：先提取Table1中Type1='13'的所有记录，作为递归的起始节点，记录初始CoId和Type2属性。
2. 递归成员：用上一轮递归得到的current_coid作为ID，去Table1中匹配对应记录；只有当当前节点的Type2='13'且CoId≠0时，才继续递归遍历，直到不满足条件自动停止。
3. 结果筛选：对每个ID的所有递归结果，筛选出符合停止条件的记录，再通过ROW_NUMBER取其中层级最深的那条（即最终的Final CoId），最后关联Table2拿到对应的Value。

该方案可处理任意多层的CoId嵌套场景，完全替代硬编码的单层连接逻辑。

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