問題描述
我想增加一列，然后根據另一列中的值停止并重新開始.
I want to increment a column and then stop and start again based on a value in another column.
例如:
我有一張桌子:
CustomerID YearMonth True_False
9000 2013-01-01 0
9001 2013-02-01 0
9002 2013-03-01 0
9003 2013-04-01 0
9004 2013-05-01 0
9005 2013-06-01 1
9006 2013-07-01 0
9007 2013-08-01 0
9008 2013-09-01 0
9009 2013-10-01 1
9010 2013-11-01 0
我想實現這一目標:
CustomerID YearMonth True_False Sequence
9000 2013-01-01 0 1
9001 2013-02-01 0 2
9002 2013-03-01 0 3
9003 2013-04-01 0 4
9004 2013-05-01 0 5
9005 2013-06-01 1 0
9006 2013-07-01 0 1
9007 2013-08-01 0 2
9008 2013-09-01 0 3
9009 2013-10-01 1 0
9010 2013-11-01 0 1
所以這是基于 True_False 列.當 True_False 為 0 時，中斷并重新開始.( True_False = 0 ) 的 Sequence 值不必為 0，也可以為 NULL.
So this is based on True_False column. When True_False is 0 then break and start over again. Sequence value for ( True_False = 0 ) doesn't need to be 0 could be NULL as well.
推薦答案
試試這個:
SELECT CustomerID, YearMonth, True_False,
CASE
WHEN True_False = 1 THEN 0
ELSE ROW_NUMBER() OVER (PARTITION BY True_False, grp
ORDER BY YearMonth)
END AS Sequence
FROM (
SELECT CustomerID, YearMonth, True_False,
ROW_NUMBER() OVER (ORDER BY YearMonth) -
ROW_NUMBER() OVER (PARTITION BY True_False
ORDER BY YearMonth) AS grp
FROM mytable ) AS t
ORDER BY YearMonth
查詢計算字段grp，該字段標識具有相同True_False 值的連續記錄的島.在外部查詢中使用此字段，我們可以枚舉這些島嶼中包含的記錄，從而獲得所需的序列號.
The query calculates field grp which identifies islands of consecutive records having the same True_False value. Using this field in an outer query we can enumerate the records contained inside these islands and thus get the required sequence number.
此處演示
這篇關于根據另一列中的值遞增然后中斷并重新開始的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！