循环遍历表和更新记录

Question

我需要根据同一个表中的多个字段修改表，并且我似乎无法正确地实现它，并且正在成为一个真正的痛苦。

我的表看起来像这样：

Id  person code product  item  delivery 
    41  254252 1368 prodname 561352401 23 
    41  254252 1368 prodname 4421252401 23 
    42  254252 1368 prodname 2501552403 23 
    42  254252 1368 prodname 5281352400 23 
    45  254251 1368 prodname 561352401 56 
    46  254251 1368 prodname 2501552403 56 
    49  254250 1368 prodname 561352401 69 
    50  254250 1368 prodname 2501552403 69 
    1357 253288 1368 prodname 4421252401 2 
    1358 253288 1368 prodname 5281352400 2 
    1359 253288 1368 prodname 9990070900 2 
    1377 253263 1220 prodname 2331252400 11 
    1378 253263 1220 prodname 2461252403 11 
    1379 253263 1220 prodname 9990070900 11 

我需要根据更新送达柱：人，代码和产品。当它应该设置这些列的一个变化值的计数器为0

我需要的输出看起来像这样：

Id  person code product  item  delivery 
    41  254252 1368 prodname 561352401 23 
    41  254252 1368 prodname 4421252401 24 
    42  254252 1368 prodname 2501552403 25 
    42  254252 1368 prodname 5281352400 26 
    45  254251 1368 prodname 561352401 56 
    46  254251 1368 prodname 2501552403 57 
    49  254250 1368 prodname 561352401 69 
    50  254250 1368 prodname 2501552403 70 
    1357 253288 1368 prodname 4421252401 2 
    1358 253288 1368 prodname 5281352400 3 
    1359 253288 1368 prodname 9990070900 4 
    1377 253263 1220 prodname 2331252400 11 
    1378 253263 1220 prodname 2461252403 12 
    1379 253263 1220 prodname 9990070900 13 

到目前为止，我有这样的：

DECLARE @Id  INT 
    DECLARE @person  INT 
    DECLARE @code  VARCHAR(15) 
    DECLARE @product VARCHAR(10) 
    DECLARE @Itemtype VARCHAR(10) 
    DECLARE @Item  VARCHAR(50) 
    DECLARE @Delivery INT 

    DECLARE sel_cursor CURSOR FOR 

    SELECT Id, person, code, product, delivery FROM [dbo].[orders] 
    WHERE Itemtype = 'ART' 
    ORDER BY person, product,Itemtype, Item, delivery desc 

    DECLARE @Counter  INT 

    OPEN sel_cursor 

    set @Counter = 0 

    FETCH NEXT FROM sel_cursor 
    INTO @Id, @person, @code, @product, @Delivery 

    WHILE @@FETCH_STATUS = 0 
    BEGIN 

    --WHILE (SELECT COUNT(*) FROM orders WHERE [email protected] AND [email protected] and [email protected]) > 0 

    BEGIN 
     UPDATE Orders 
     SET delivery = (delivery + @Counter) WHERE [email protected] AND [email protected] AND [email protected] 

     set @Counter = @Counter + 1 
    END 

    FETCH NEXT FROM sel_cursor 
    INTO @Id, @person, @code, @product, @Delivery 

    set @Counter = 0 

    END 

    CLOSE sel_cursor 
    DEALLOCATE sel_cursor 
    END 

Answer 1

DECLARE @t TABLE (
    Id INT, 
    person INT, 
    code VARCHAR(10), 
    product VARCHAR(20), 
    item VARCHAR(20), 
    delivery INT 
) 

INSERT INTO @t 
VALUES 
    (41 , 254252, 1368, 'prodname', '561352401 ', 23), 
    (41 , 254252, 1368, 'prodname', '4421252401', 23), 
    (42 , 254252, 1368, 'prodname', '2501552403', 23), 
    (42 , 254252, 1368, 'prodname', '5281352400', 23), 
    (45 , 254251, 1368, 'prodname', '561352401 ', 56), 
    (46 , 254251, 1368, 'prodname', '2501552403', 56), 
    (49 , 254250, 1368, 'prodname', '561352401 ', 69), 
    (50 , 254250, 1368, 'prodname', '2501552403', 69), 
    (1357, 253288, 1368, 'prodname', '4421252401', 2), 
    (1358, 253288, 1368, 'prodname', '5281352400', 2), 
    (1359, 253288, 1368, 'prodname', '9990070900', 2), 
    (1377, 253263, 1220, 'prodname', '2331252400', 11), 
    (1378, 253263, 1220, 'prodname', '2461252403', 11), 
    (1379, 253263, 1220, 'prodname', '9990070900', 11) 

;WITH cte AS 
(
    SELECT *, RowNum = ROW_NUMBER() OVER (
        PARTITION BY Person, Code, product 
        ORDER BY 1/0) - 1 
    FROM @t 
) 
UPDATE cte 
SET delivery += RowNum 

SELECT * FROM @t 

输出 -

Id   person  code  product item   delivery 
----------- ----------- ---------- ---------- ------------- ----------- 
41   254252  1368  prodname 561352401  23 
41   254252  1368  prodname 4421252401 24 
42   254252  1368  prodname 2501552403 25 
42   254252  1368  prodname 5281352400 26 
45   254251  1368  prodname 561352401  56 
46   254251  1368  prodname 2501552403 57 
49   254250  1368  prodname 561352401  69 
50   254250  1368  prodname 2501552403 70 
1357  253288  1368  prodname 4421252401 2 
1358  253288  1368  prodname 5281352400 3 
1359  253288  1368  prodname 9990070900 4 
1377  253263  1220  prodname 2331252400 11 
1378  253263  1220  prodname 2461252403 12 
1379  253263  1220  prodname 9990070900 13