Redshift中使用递归CTE获取所有ID的超级父ID问题修正

你的递归CTE存在两处问题，导致结果不符合预期：

• 顶层节点（parent_id为null）的super_parent_id被错误设置为自身ID，而不是期望的null；
• 递归阶段逻辑有误，当父节点是顶层节点时，子节点的super_parent_id会继承为null，而非顶层节点的ID。

以下是修正后的代码：

WITH RECURSIVE ParentHierarchy AS (
    -- 初始步骤：处理无父节点的顶层节点
    SELECT 
        id,
        parent_id,
        id AS root_id,  -- 记录当前顶层节点为该树的根ID
        NULL AS super_parent_id  -- 顶层节点无超级父ID，设为null
    FROM org
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归步骤：遍历子节点，继承父节点所属树的根ID作为超级父ID
    SELECT 
        t.id,
        t.parent_id,
        ph.root_id,  -- 继承父节点对应的根ID
        ph.root_id AS super_parent_id  -- 子节点的超级父ID就是所属树的顶层根节点ID
    FROM org t
    JOIN ParentHierarchy ph ON t.parent_id = ph.id
)
SELECT id, parent_id, super_parent_id
FROM ParentHierarchy
ORDER BY id;

结果验证 #

执行上述代码后，会得到你期望的输出：

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