时间戳

首页 > 解决方案 > 使用连接的先前值更新列？

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

问题描述

你好亲爱的 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 语句做到这一点的方法吗？

标签： sqlsql-server

解决方案

一种方法是使用递归公用表表达式 (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;

推荐阅读