您需要根据Retry
列中的值定义组,并对行进行适当编号:
测试数据:
SELECT *
INTO Data
FROM (VALUES
(1, 12, 'False', 0),
(2, 12, 'True', 0),
(3, 12, 'True', 0),
(4, 12, 'False', 0),
(5, 12, 'True', 0),
(6, 12, 'True', 0),
(7, 12, 'False', 0)
) v (TxnId, UserId, Retry, AttemptNumber)
声明:
; WITH UpdateCTE AS (
SELECT
TxnId, UserId, Retry, AttemptNumber,
ROW_NUMBER()
OVER (PARTITION BY UserId, GroupId ORDER BY TxnId) - 1 AS NewAttemptNumber
FROM (
SELECT
*,
SUM(CASE WHEN Retry = 'False' THEN 1 ELSE 0 END)
OVER (PARTITION BY UserId ORDER BY TxnId) AS GroupId
FROM Data
) t
)
UPDATE UpdateCTE
SET AttemptNumber = NewAttemptNumber
结果:
TxnId |
UserId |
Retry |
AttemptNumber |
1 |
12 |
False |
0 |
2 |
12 |
True |
1 |
3 |
12 |
True |
2 |
4 |
12 |
False |
0 |
5 |
12 |
True |
1 |
6 |
12 |
True |
2 |
7 |
12 |
False |
0 |