问题描述
我想增加一列，然后根据另一列中的值停止并重新开始.
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.
此处演示
这篇关于根据另一列中的值递增然后中断并重新开始的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！