问题描述
我想增加一列,然后根据另一列中的值停止并重新开始.
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.
此处演示