sql - 使用连接的先前值更新列？

Question

你好亲爱的 Stackoverflow SQL 大师。
使用这个简单的数据模型:

create table test(Id INT, Field1 char(1), Field2 varchar(max));

insert into test (id, Field1) values (1, 'a');
insert into test (id, Field1) values (2, 'b');
insert into test (id, Field1) values (3, 'c');
insert into test (id, Field1) values (4, 'd');

我可以在一个简单的 TSQL 匿名块中使用 Field1 和 Field2 连接先前的值来更新 Field2，如下所示:

BEGIN 
    DECLARE @CurrentId INT;
    DECLARE @CurrentField1 char(1);
    DECLARE @Field2 varchar(max) = NULL;

    DECLARE cur CURSOR FOR   
        SELECT  id, Field1
        FROM    test
        ORDER BY id;

    OPEN cur  
    FETCH NEXT FROM cur INTO @CurrentId, @CurrentField1;
  
    WHILE @@FETCH_STATUS = 0  
    BEGIN
        SET @Field2 = CONCAT(@Field2, @CurrentId, @CurrentField1);

        UPDATE test
        SET Field2 = @Field2
        WHERE Id = @CurrentId;

        FETCH NEXT FROM cur INTO @CurrentId, @CurrentField1;
    END  
  
    CLOSE cur;
    DEALLOCATE cur;
END
GO

给我想要的结果:

select * from test;

Id  Field1  Field2
1   a       1a
2   b       1a2b
3   c       1a2b3c
4   d       1a2b3c4d

我想使用单个 UPDATE 命令获得相同的结果以避免 CURSOR。
我认为 LAG() 函数是可能的:

UPDATE test set Field2 = NULL; --reset data

UPDATE test
SET Field2 = NewValue.NewField2
FROM  (
    SELECT  CONCAT(Field2, Id, ISNULL(LAG(Field2,1) OVER (ORDER BY Id), '')) AS NewField2,
            Id
    FROM    test
) NewValue
WHERE   test.Id = NewValue.Id;

但这给了我这个:

select * from test;

Id  Field1  Field2
1   a       1
2   b       2
3   c       3
4   d       4

Field2 未正确更新为 Id+Field1+(以前的 Field2)。
更新结果对我来说是合乎逻辑的，因为当 LAG() 函数重新选择表中的值时，该值尚未更新。
您认为它们是一种使用单个 SQL 语句执行此操作的方法吗？

Answer 1

一种方法是使用递归公用表表达式 (rCTE) 来迭代数据。这假设 Id 的所有值是连续的:

WITH rCTE AS(
    SELECT Id,
           Field1,
           CONVERT(varchar(MAX),CONCAT(ID,Field1)) AS Field2
    FROM dbo.test
    WHERE ID = 1
    UNION ALL
    SELECT t.Id,
           t.Field1,
           CONVERT(varchar(MAX),CONCAT(r.Field2,t.Id,t.Field1)) AS Field2
    FROM dbo.test t
         JOIN rCTe r ON t.id = r.Id + 1)
SELECT *
FROM rCTe;

如果它们不是连续的，您可以使用 CTE 首先对行进行编号:

WITH RNs AS(
    SELECT Id,
           Field1,
           ROW_NUMBER() OVER (ORDER BY ID) AS RN
    FROM dbo.Test),
rCTE AS(
    SELECT Id,
           Field1,
           CONVERT(varchar(MAX),CONCAT(ID,Field1)) AS Field2,
           RN
    FROM RNs
    WHERE ID = 1
    UNION ALL
    SELECT RN.Id,
           RN.Field1,
           CONVERT(varchar(MAX),CONCAT(r.Field2,RN.Id,RN.Field1)) AS Field2,
           RN.RN
    FROM RNs RN
         JOIN rCTe r ON RN.RN = r.RN + 1)
SELECT Id,
       Field1,
       Field2
FROM rCTe;