用同一表中的下一个开始时间更新结束时间

Question

我在SQL server中有一个表:

name varchar(10)
start_time timestamp
end_time timestamp

表中有如下数据:

John, '2014-05-26 00-00-01', null
John, '2014-05-25 15-00-01', null
Tom,  '2014-05-25 16-00-01', null
John, '2014-05-25 09-00-01', null
Tom,  '2014-05-25 03-00-01', null
..

我想有一个SQL更新end_time与下一个start_time从同一个人，更新后，结果如下:

John, '2014-05-26 00-00-01', null
John, '2014-05-25 15-00-01', '2014-05-26 00-00-01'
Tom,  '2014-05-25 16-00-01', null
John, '2014-05-25 09-00-01', '2014-05-25 15-00-01'
Tom,  '2014-05-25 03-00-01', '2014-05-25 16-00-01'

是否有任何简单的方法在SQL?我想需要内连接吗?

update mytable t1
set t1.end_time = min(t2.start_time)
inner join mytable t2 on t1.name = t2.name where t1.start_time < t2.start_time and t1.end_time = null;

Answer 1

这可能对你有帮助。

DECLARE @TABLE TABLE 
        (
        NAME VARCHAR(10), 
        START_TIME DATETIME, 
        END_TIME DATETIME
        )
INSERT  INTO @TABLE (NAME,START_TIME,END_TIME) VALUES 
('JOHN', '2014-05-26 00:00:01',NULL),
('JOHN', '2014-05-25 15:00:01',NULL),
('TOM',  '2014-05-25 16:00:01',NULL),
('JOHN', '2014-05-25 09:00:01',NULL),
('TOM',  '2014-05-25 03:00:01',NULL)
UPDATE  A
SET     A.END_TIME = LU.START_TIME
FROM    (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY START_TIME DESC) [ROW] 
        FROM    @TABLE
        ) A 
LEFT JOIN   (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY START_TIME DESC) [ROW] 
        FROM    @TABLE
        ) LU 
ON      A.NAME = LU.NAME 
AND     A.ROW = LU.ROW + 1
SELECT * FROM @TABLE

Answer 2

下面的查询使用相关子查询(如果可用，也可以使用cross apply或lead()函数)获取下一个开始时间。

select t.*,
       (select top 1 t2.start_time
        from t2.name = t.name and t2.start_time > t.start_time
        order by t2.start_time asc
       ) as next_start_time
from mytable t;

SQL Server的一个非常好的特性是可更新的CTE。所以你可以在update语句中使用:

with toupdate as (
      select t.*,
             (select top 1 t2.start_time
              from t2.name = t.name and t2.start_time > t.start_time
              order by t2.start_time asc
             ) as next_start_time
      from mytable t
     )
update toupdate
    set end_time = next_start_time;