当一个的结束时间是另一个的开始时间时合并行

Question

我在编写查询时遇到困难。我需要将时间连续状态的行合并为一个状态。例如，给定数据：

start                    end                      state   seconds
2011-04-21 08:13:30.000  2011-04-21 08:18:00.000  STATE1  270
2011-04-21 08:18:00.000  2011-04-21 08:22:30.000  STATE1  270
2011-04-21 08:22:30.000  2011-04-21 08:26:26.000  STATE1  236
2011-04-21 08:26:26.000  2011-04-21 08:26:47.000  STATE2  21
2011-04-21 08:26:47.000  2011-04-21 08:27:30.000  STATE3  43
2011-04-21 08:27:30.000  2011-04-21 08:28:20.000  STATE1  50
2011-04-21 08:40:30.000  2011-04-21 08:41:00.000  STATE1  30

我只需要合并行，row2.state = row1.state AND row2.start = row1.end并得出状态的总体开始和结束时间。结果应为：

start                    end                      state   seconds
2011-04-21 08:13:30.000  2011-04-21 08:26:26.000  STATE1  776
2011-04-21 08:26:26.000  2011-04-21 08:26:47.000  STATE2  21
2011-04-21 08:26:47.000  2011-04-21 08:27:30.000  STATE3  43
2011-04-21 08:27:30.000  2011-04-21 08:28:20.000  STATE1  50
2011-04-21 08:40:30.000  2011-04-21 08:41:00.000  STATE1  30

这是示例SQL：

CREATE TABLE Data (
    ID int IDENTITY(1,1) not null,
    Date DateTime not null,
    State nvarchar(40) not null,
    Seconds int not null,
    PRIMARY KEY(ID)
);
INSERT INTO Data(Date,State,Seconds) VALUES('2011-04-21 08:13:30.000', 'STATE1', 270)
INSERT INTO Data(Date,State,Seconds) VALUES('2011-04-21 08:18:00.000', 'STATE1', 270)
INSERT INTO Data(Date,State,Seconds) VALUES('2011-04-21 08:22:30.000', 'STATE1', 236)
INSERT INTO Data(Date,State,Seconds) VALUES('2011-04-21 08:26:26.000', 'STATE2', 21)
INSERT INTO Data(Date,State,Seconds) VALUES('2011-04-21 08:26:47.000', 'STATE3', 43)
INSERT INTO Data(Date,State,Seconds) VALUES('2011-04-21 08:27:30.000', 'STATE1', 50)
INSERT INTO Data(Date,State,Seconds) VALUES('2011-04-21 08:40:30.000', 'STATE1', 30)

SELECT Date as 'start', DATEADD(s,Seconds,Date) as 'end', State, Seconds FROM Data

提前致谢！

Answer 1

试试这个（> = SQL Server 2005）：

WITH qry AS
(
    SELECT  a.*
                    ,ROW_NUMBER() OVER (ORDER BY [start]) rn
    FROM    (SELECT Date as 'start', DATEADD(s,Seconds,Date) as 'end', State, Seconds FROM Data) a
)
SELECT  DISTINCT MIN(a.start) OVER(PARTITION BY a.State, a.[end] - ISNULL(b.start, a.start)) ,
                MAX(a.[end] ) OVER(PARTITION BY a.State, a.[end] - ISNULL(b.start, a.start)) ,
                a.state
                ,SUM(a.Seconds) OVER(PARTITION BY a.State, a.[end] - ISNULL(b.start, a.start))

  FROM qry a LEFT JOIN qry b
        ON a.rn + 1 = b.rn
   AND a.[end] = b.start