如何在SQL的游标结果集中获取行的最新值

Nilesh Nikumbh 发表于 Dev

尼罗什·尼古姆（Nilesh nikumbh）

我在以下表的Select语句中使用的同一表上使用Cursor进行某些操作，

CREATE TABLE #nsn (ID INT IDENTITY(1,1)
,Val1 Varchar(MAx)
,Val2 Varchar(MAx)
)

INSERT INTO #nsn(Val1,VAl2) select 'A1','A2'
INSERT INTO #nsn(Val1,VAl2) select 'B1','B2'
INSERT INTO #nsn(Val1,VAl2) select 'C1','C2'
INSERT INTO #nsn(Val1,VAl2) select 'D1','D2'
INSERT INTO #nsn(Val1,VAl2) select 'E1','F2'

SELECT * From #nsn
ID  Val1    VAl2
1|  A1  |   A2
2|  B1  |   B2
3|  C1  |   C2
4|  D1  |   D2
5|  E1  |   F2

使用游标更新Val2栏，

DECLARE @ID INT 
        ,@Val1 Varchar(MAx)
        ,@Val2 Varchar(MAx)

DECLARE cursor_Latest CURSOR
FOR SELECT  ID,Val1,Val2
    FROM    #nsn 
    ORder by 1 asc
OPEN cursor_Latest
FETCH NEXT FROM cursor_Latest INTO  @ID , @Val1 ,@Val2
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    UPDATE #nsn
    SET Val2 = Val2 +'-'+ @Val1 +'-'+@Val2
    WHERE ID = @ID+1

    FETCH NEXT FROM cursor_Latest INTO  @ID , @Val1 ,@Val2
END
close cursor_Latest
DEALLOCATE cursor_Latest

其结果是

SELECT * From #nsn
ID  Val1    VAl2
1|  A1  |   A2
2|  B1  |   B2-A1-A2
3|  C1  |   C2-B1-B2
4|  D1  |   D2-C1-C2
5|  E1  |   F2-D1-D2

但是我期望结果如下

ID  Val1    VAl2
1|  A1  |   A2
2|  B1  |   B2-A1-A2
3|  C1  |   C2-B1-B2-A1-A2
4|  D1  |   D2-C1-C2-B1-B2-A1-A2
5|  E1  |   F2-D1-D2-C1-C2-B1-B2-A1-A2

那么这段代码有什么问题吗？或Cursor是否有任何记录存储的东西，因为它没有获取新行的最新更新值。

提前谢谢您的帮助。

婴儿车

为了达到预期的效果，您应该尝试以下操作，

DECLARE @ID       INT
       ,@Val1     VARCHAR(MAX)
       ,@Val2     VARCHAR(MAX)

DECLARE cursor_Latest CURSOR  
FOR
    SELECT ID,Val1,Val2 FROM   #nsn
    ORDER BY 1 ASC

OPEN cursor_Latest
FETCH NEXT FROM cursor_Latest INTO  @ID , @Val1 ,@Val2
WHILE (@@FETCH_STATUS<>-1)
BEGIN
    IF @ID=1
    BEGIN
        UPDATE #nsn
        SET    Val2     = Val2+'-'+@Val1+'-'+@Val2
        WHERE  ID       = @ID+1
    END
    ELSE
    BEGIN
        UPDATE #nsn
        SET    Val2   = Val2+'-'+@Val1+'-'+@Val2+'-'+( SELECT Val1+'-'+Val2 FROM   #nsn WHERE  ID     = @ID-1)
        WHERE  ID                = @ID+1
    END 

    FETCH NEXT FROM cursor_Latest INTO @ID , @Val1 ,@Val2
END
CLOSE cursor_Latest
DEALLOCATE cursor_Latest

SELECT * FROM   #nsn
DROP TABLE  #nsn