sql-server - 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

类似问题:

SQL Server 2012: Conditionally Incrementing a counter user ROW_NUMBER()

我正在使用 SQL 2014

Answer 1

这似乎产生了您想要的结果。在尝试精通编写 SQL 时，尝试从整个结果集的角度思考而不是考虑“逐行”处理是最有益的。

因此，我将您的规范重写为:

EVENT_SEQ is the number of something events that occur before or on the current row, +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