SQL Azure中同更新流程里基于已更新值计算列的实现问题

环境信息 #

Microsoft SQL Azure (RTM) - 12.0.2000.8 Jun 19 2024 16:01:48 Copyright (C) 2022 Microsoft Corporation

临时表结构与初始化数据 #

现有人员层级临时表##People1，结构及初始化数据如下：

DROP TABLE IF EXISTS ##People1

CREATE TABLE ##People1
( 
    RowID      INT PRIMARY KEY IDENTITY(1, 1) NOT NULL, 
    PersonID   INT NULL, 
    PersonName VARCHAR(100) NULL, 
    ReportsTo  VARCHAR(100) NULL, 
    SeeMyData  VARCHAR(MAX) NULL
)
GO

INSERT INTO ##People1 (PersonID, PersonName, ReportsTo)
VALUES (7036, 'Liesl', NULL),
       (4049, 'Friedrich', 7036),
       (197, 'Louisa', 4049),
       (2303, 'Kurt', 197),
       (3409, 'Brigitta', 2303),
       (5686, 'Marta', 4049),
       (533, 'Gretl', 5686),
       (5204, 'Mike', 533),
       (4063, 'Sara', 3409),
       (1928, 'Tom', 197),
       (7013, 'Jerry', 1928),
       (7033, 'Sue', 533)
GO

需求说明 #

按层级计算并更新SeeMyData字段，规则为：

SeeMyData = CONCAT(上级的SeeMyData字符串,'-', 上级的PersonID)

预期结果为上级链的PersonID拼接字符串，例如RowID=2的SeeMyData为7036，RowID=3为7036-4049。

遇到的问题 #

尝试复制表关联更新及LAG函数均失败，更新值无法实时写入，后续行仍读取到NULL值。失败代码如下：

DROP TABLE IF EXISTS ##People2

SELECT * 
INTO ##People2 
FROM ##People1
GO

UPDATE ##People1
SET ##People1.seemydata = CONCAT(p2.SeeMyData, '-', p1.ReportsTo) 
FROM ##People1 p1
JOIN ##People2 p2 ON p1.reportsto = p2.PersonID

可行实现方案 #

由于需要递归获取上级链并实时使用已计算的层级值，**递归CTE（Common Table Expression）**是最优解决方案，它可以逐层遍历层级结构并构建完整的上级ID链。

实现代码 #

WITH RecursivePeople AS (
    -- 锚点成员：顶层节点（无上级）
    SELECT 
        PersonID,
        PersonName,
        ReportsTo,
        CAST('' AS VARCHAR(MAX)) AS SeeMyData  -- 顶层节点无上级，初始为空
    FROM ##People1
    WHERE ReportsTo IS NULL

    UNION ALL

    -- 递归成员：逐层向下遍历子节点
    SELECT 
        p.PersonID,
        p.PersonName,
        p.ReportsTo,
        -- 拼接上级的SeeMyData和上级PersonID，处理空值避免多余的横杠
        CAST(CONCAT(rp.SeeMyData, CASE WHEN rp.SeeMyData <> '' THEN '-' ELSE '' END, rp.PersonID) AS VARCHAR(MAX)) AS SeeMyData
    FROM ##People1 p
    INNER JOIN RecursivePeople rp ON p.ReportsTo = CAST(rp.PersonID AS VARCHAR(100))  -- 类型转换匹配ReportsTo的VARCHAR类型
)
-- 更新原表的SeeMyData字段
UPDATE p
SET p.SeeMyData = rp.SeeMyData
FROM ##People1 p
INNER JOIN RecursivePeople rp ON p.PersonID = rp.PersonID;

验证结果 #

执行以下查询查看更新后的完整数据：

SELECT RowID, PersonID, PersonName, ReportsTo, SeeMyData
FROM ##People1
ORDER BY RowID;

方案说明 #

1. 锚点成员：先筛选出没有上级的顶层节点，初始SeeMyData设为空字符串。
2. 递归成员：通过关联子节点的ReportsTo与父节点的PersonID（注意类型转换，因为ReportsTo是VARCHAR类型），逐层拼接父节点的SeeMyData和父节点ID，确保每个节点都使用已计算完成的父节点值。
3. 更新操作：将递归CTE生成的完整层级链更新回原表对应的行。

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