問題描述
我想增加一列，然后根據(jù)另一列中的值停止并重新開始.
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
我想實(shí)現(xiàn)這一目標(biāo):
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 列.當(dāng) True_False 為 0 時(shí)，中斷并重新開始.( 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.
推薦答案
試試這個(gè):
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
查詢計(jì)算字段grp，該字段標(biāo)識(shí)具有相同True_False 值的連續(xù)記錄的島.在外部查詢中使用此字段，我們可以枚舉這些島嶼中包含的記錄，從而獲得所需的序列號(hào).
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.
此處演示
這篇關(guān)于根據(jù)另一列中的值遞增然后中斷并重新開始的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！