PostgreSQL中实现递归CTE更新列的问题求助

原始数据表 #

需求逻辑 #

需要修正SUWH不为空的行的RESP字段，规则如下：

• 跳过SUWH为空的行（无需更新）；
• 对SUWH非空的行，回溯查找关联行：以当前行的SUWH作为WHLO、相同ITNO的行，重复此过程直到找到SUWH为空的行，将当前行的RESP更新为最终找到的行的RESP。

目标结果表 #

尝试的错误代码 #

之前使用ChatGPT生成的递归CTE未成功，代码如下：

WITH RecursiveUpdateCTE AS (
    SELECT 
        T1.*, 
        CASE 
            WHEN T1.SUWH IS NOT NULL THEN T1.SUWH 
            ELSE T1.WHLO 
        END AS CurrentWHLO,
        0 AS Iteration
    FROM YourTableName T1

    UNION ALL

    SELECT 
        T2.*, 
        CASE 
            WHEN T2.SUWH IS NOT NULL THEN T2.SUWH 
            ELSE T2.WHLO 
        END AS CurrentWHLO,
        Iteration + 1
    FROM RecursiveUpdateCTE T2
    WHERE T2.SUWH IS NOT NULL
)
UPDATE YourTableName
SET RESP = (
    SELECT TOP 1 RUC.RESP
    FROM RecursiveUpdateCTE RUC
    WHERE RUC.ITNO = YourTableName.ITNO
    ORDER BY RUC.Iteration DESC
)
WHERE SUWH IS NULL;

正确解决方案 #

递归CTE实现回溯查找 #

递归CTE需要从每个待更新的行出发，向上追溯到根节点（SUWH为空的行），记录根节点的RESP值。以下是适配SQL Server的正确实现（其他数据库语法略有差异）：

-- 递归CTE追溯每个行的根节点RESP
WITH WHHierarchy AS (
    -- 锚点成员：初始化所有行的追溯起点
    SELECT 
        WHLO,
        ITNO,
        SUWH,
        WHLO AS CurrentTraceWHLO,
        RESP AS RootRESP,
        0 AS Depth
    FROM YourTableName

    UNION ALL

    -- 递归成员：继续向上追溯上级节点，直到找到根节点
    SELECT 
        h.WHLO,
        h.ITNO,
        h.SUWH,
        t.SUWH AS CurrentTraceWHLO,
        t.RESP AS RootRESP,
        h.Depth + 1 AS Depth
    FROM WHHierarchy h
    JOIN YourTableName t 
        ON t.WHLO = h.CurrentTraceWHLO 
        AND t.ITNO = h.ITNO
    WHERE t.SUWH IS NOT NULL -- 未到根节点时继续递归
)
-- 筛选每个WHLO+ITNO组合的最终追溯结果
, FinalRootRESP AS (
    SELECT 
        WHLO,
        ITNO,
        RootRESP,
        ROW_NUMBER() OVER (PARTITION BY WHLO, ITNO ORDER BY Depth DESC) AS rn
    FROM WHHierarchy
)
-- 更新需要修正的行
UPDATE YourTableName
SET RESP = f.RootRESP
FROM YourTableName yt
JOIN FinalRootRESP f 
    ON yt.WHLO = f.WHLO 
    AND yt.ITNO = f.ITNO
WHERE yt.SUWH IS NOT NULL -- 只更新SUWH非空的行
AND f.rn = 1;

代码说明 #

1. 锚点成员：为每一行初始化追溯起点，将自身WHLO作为初始追溯目标，自身RESP作为临时根节点值。
2. 递归成员：通过关联找到当前追溯目标对应的行，若该行存在上级SUWH，则更新追溯目标为SUWH，同时替换根节点RESP为该行的RESP，直到找到SUWH为空的根节点。
3. FinalRootRESP：用ROW_NUMBER()筛选每个WHLO+ITNO组合中追溯深度最大的行，即最终找到根节点的结果。
4. UPDATE语句：仅更新SUWH非空的行，将其RESP替换为根节点的RESP值。

执行后数据表将与目标结果表完全一致，SUWH为空的行保持原有值不变。

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