sql - 如何增加对另一个列值的列关注

Question

我有以下数据:

(1,'20120615 8:03:43 PM'),
(1,'20120615 8:03:43 PM'),
(1,'20120615 8:03:48 PM'),
(1,'20120615 8:03:53 PM'),
(0,'20120615 8:03:58 PM'),
(1,'20120615 8:04:03 PM'),
(1,'20120615 8:04:08 PM'),
(1,'20120615 8:04:13 PM'),
(1,'20120615 8:04:18 PM'),
(0,'20120615 8:04:23 PM'),
(1,'20120615 8:04:28 PM'),
(1,'20120615 8:04:33 PM');

我想要的结果是:

(1,'20120615 8:03:43 PM', 1),
(1,'20120615 8:03:43 PM', 1),
(1,'20120615 8:03:48 PM', 1),
(1,'20120615 8:03:53 PM', 1),
(0,'20120615 8:03:58 PM', 0),
(1,'20120615 8:04:03 PM', 2),
(1,'20120615 8:04:08 PM', 2),
(1,'20120615 8:04:13 PM', 2),
(1,'20120615 8:04:18 PM', 2),
(0,'20120615 8:04:23 PM', 0),
(1,'20120615 8:04:28 PM', 3),
(1,'20120615 8:04:33 PM', 3);

换句话说，我想按照上面的顺序对除 0 以外的数据进行分组。

Answer 1

这是一个想法。为每个组的开始位置创建一个标志。这实际上是上面数据中的 1 - col1。然后取这个标志的累加和。对于不是 0 的值，这是组。以下查询采用这种方法，使用相关子查询进行计算:

select t.col1, t.dt,
       (case when t.col1 = 0 then 0 else 1+grouping end)
from (select t.*,
             (select sum(1-col1)
              from t t2
              where t2.dt <= t.dt
             ) as grouping
      from t
     ) t;

在 SQL Server 2012 中，您可以使用累计和来执行此操作:

select t.col1, t.dt,
       (case when col1 = 0 then 0
             else 1+sum(1 - col1) over (order by dt)
        end)
from t;