MSSQL创建并有条件地增加列

Question

我正在获取一些数据(进入一个#temp表)，这些数据具有一些逻辑顺序。

当我提取数据时，我想添加一个新的序列/计数器，它只有在满足其他字段中的某些条件时才会增加。

理想的情况是这样的：

DECLARE @counter int = 0;

SELECT Item, Date, Event, 
@counter = @counter + 
    (CASE 
        WHEN Event = 'Something' THEN 1 
        ELSE 0 
    END) AS EVENT_SEQ
INTO #tempTable
FROM  MyData
ORDER BY Item, Date

SQL给出了两个错误：

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

或

A SELECT INTO statement cannot contain a SELECT statement that assigns values to a variable.

期望的结果

ITEM    DATE        EVENT       EVENT_SEQ
blah    2015-01-01  nothing     1
blah    2015-01-02  nothing     1
blah    2015-01-03  nothing     1
blah    2015-01-04  something   2
blah    2015-01-05  nothing     2
blah    2015-01-06  nothing     2
blah    2015-01-07  something   3
blah    2015-01-08  nothing     3
blah    2015-01-09  nothing     3
blah    2015-01-10  nothing     3
blah    2015-01-11  nothing     3
blah    2015-01-12  something   4
blah    2015-01-13  something   5
blah    2015-01-14  nothing     5
blah    2015-01-15  nothing     5
blah    2015-01-16  nothing     5
blah    2015-01-17  nothing     5
blah    2015-01-18  nothing     5
blah    2015-01-19  nothing     5
blah    2015-01-20  something   6
blah    2015-01-21  something   7
blah    2015-01-22  nothing     7

类似的问题：

NUMBER()

我正在使用SQL 2014

Answer 1

这似乎产生了你想要的结果。当试图熟练地编写SQL时，尝试考虑整个结果集是最有益的，而不是考虑“逐行”处理。

因此，我把你的规格重铸为：

EVENT_SEQ是当前行之前或上发生的something事件数，+1

declare @t table (ITEM char(4), [Date] date, [Event] varchar(9))
insert into @T(ITEM,[DATE],[EVENT]) values
('blah','20150101','nothing'),
('blah','20150102','nothing'),
('blah','20150103','nothing'),
('blah','20150104','something'),
('blah','20150105','nothing'),
('blah','20150106','nothing'),
('blah','20150107','something'),
('blah','20150108','nothing'),
('blah','20150109','nothing'),
('blah','20150110','nothing'),
('blah','20150111','nothing'),
('blah','20150112','something'),
('blah','20150113','something'),
('blah','20150114','nothing'),
('blah','20150115','nothing'),
('blah','20150116','nothing'),
('blah','20150117','nothing'),
('blah','20150118','nothing'),
('blah','20150119','nothing'),
('blah','20150120','something'),
('blah','20150121','something'),
('blah','20150122','nothing')

select *,
  SUM(CASE WHEN [event]='something' THEN 1 ELSE 0 END) OVER
  (ORDER BY item,date
   ROWS BETWEEN UNBOUNDED PRECEDING
            AND CURRENT ROW)+1 as EVENT_SEQ
from @t
ORDER BY item,date

希望您能够看到，我几乎直接将其转换为一个窗口聚合函数(SUM() OVER())。结果：

ITEM Date       Event     EVENT_SEQ
---- ---------- --------- -----------
blah 2015-01-01 nothing   1
blah 2015-01-02 nothing   1
blah 2015-01-03 nothing   1
blah 2015-01-04 something 2
blah 2015-01-05 nothing   2
blah 2015-01-06 nothing   2
blah 2015-01-07 something 3
blah 2015-01-08 nothing   3
blah 2015-01-09 nothing   3
blah 2015-01-10 nothing   3
blah 2015-01-11 nothing   3
blah 2015-01-12 something 4
blah 2015-01-13 something 5
blah 2015-01-14 nothing   5
blah 2015-01-15 nothing   5
blah 2015-01-16 nothing   5
blah 2015-01-17 nothing   5
blah 2015-01-18 nothing   5
blah 2015-01-19 nothing   5
blah 2015-01-20 something 6
blah 2015-01-21 something 7
blah 2015-01-22 nothing   7

Answer 2

可以使用此查询在临时表中插入数据。

select Item,[Date] ,[Event],
  SUM(CASE WHEN event = 'something' THEN 1 ELSE 0 END)  OVER  (ORDER BY Item,date  )+1 as sequence INTO #tempTable
from MyData
ORDER BY item,date