SQL Server 2012——基于行到行比较更新列

I have a table that contains dates and times. For example columns are Date, ExTime, NewTime, Status. I am ordering them based on a expkey column that makes them show in the right order.

我有一个包含日期和时间的表。例如，列是日期、ExTime、NewTime、状态。我是根据一个可以让它们按正确顺序显示的扩展列来排序的。

I want to do a row by row comparison and compare the second row column of extime to the first row column NewTime. If extime < Newtime then I want to update status with a "1". And then traverse through the table row by row where second row in the above example becomes the first and a new second is pull and used. Here is a sample of what I have now - but it is not hitting and working all all of the rows for some reason.

我想逐行进行比较，并将extime的第二行列与第一行列NewTime进行比较。如果extime < Newtime，那么我想用“1”更新状态。然后逐行遍历表，上面示例中的第二行成为第一行，并使用新的第二行。这里有一个我现在所拥有的样本——但由于某种原因，它并没有对所有的行进行攻击和处理。

UPDATE t
SET t.Status = 1
FROM MyTable t
CROSS APPLY (SELECT TOP 1 NewTime
             FROM MyTable
             WHERE ID = t.ID AND [Date] = t.[Date]
             ORDER BY ExpKey) t1
WHERE t.Extime < t1.NewTime

This is not hitting all the rows like I want it to. I have the where clause comparing fields ID and Date to insure that the rows are attached to the same person. If the ID or Dates are not the same it is not attached to the same person so I would not want to update the status. So basically if the ID of Row 2 = ID of Row 1 and Date of Row 2 = Date of Row 1 I want to compare extime of row 2 and see if it is less than newtime of Row 1 - if so then update the status field of row 2.

这并不是像我希望的那样去打击所有的行。我有where子句比较字段ID和日期，以确保将行附加到同一个人。如果ID或日期不相同，则不会附加到相同的人，因此我不想更新状态。基本上如果行2 = ID的ID的第一行和第二行日期=行日期1我想比较extime第2行,看看它是否小于新行1 -如果然后更新行2的状态字段。

Any help in figuring out why this sort of works but not on all would be appreciated.

任何有助于弄清楚为什么这类作品，但不是全部将会受到赞赏。

Ad.

广告。

2 个解决方案

#1

On SQL Server 2012 you can easily update status with window function lag():

在SQL Server 2012上，您可以使用window function lag()轻松更新状态:

with cte as (
    select
        extime,
        lag(newtime) over(partition by id, date order by expKey) as newtime,
        status
    from table1
)
update cte set status = 1 where extime < newtime;

sql fiddle demo

sql小提琴演示

#2

I haven't tested this, but I've dealt with similar issues of comparing adjacent rows. I put this together off-the-cuff, so it may need tweaking, but give it a try.

我还没有测试过这个，但是我已经处理了类似的比较相邻行的问题。我把它即兴拼凑在一起，所以它可能需要调整，但请尝试一下。

;WITH CTE AS
(   SELECT ID,[Date],ExpKey,ExTime,NewTime,
    ROW_NUMBER()OVER(PARTITION BY ID,[Date] ORDER BY ExpKey) AS Sort
    FROM MyTable
)
UPDATE row2
SET row2.[Status] = 2
WHERE row2.ExTime < row1.NewTime
FROM CTE row2
CROSS JOIN CTE row1
    ON row1.ID = row2.ID
    AND row1.[Date] = row2.[Date]
    AND row1.Sort = row2.Sort-1 --Join to prior row

#1