更新中间行 [英] Update intermediate rows

问题描述

我有一个这样的数据集:

Hi I have a data set like this:

Date                    ID
2015-06-17 15:57:00.000 1
NULL                    2
NULL                    3
NULL                    4
NULL                    5
NULL                    6
2015-06-17 15:58:00.000 7
NULL                    8
NULL                    9
NULL                    10
NULL                    11
NULL                    12
2015-06-17 17:50:04.000 13
NULL                    14
2015-06-17 17:51:00.000 16
NULL                    17
2015-06-17 17:52:03.000 19
NULL                    20
2015-06-17 17:52:04.000 22
NULL                    23
2015-06-17 17:52:04.000 25
NULL                    26
2015-06-17 17:52:04.000 28
NULL                    29

如您所见，它们是按顺序排列的(升序)，但许多日期为 NULL

As you can see, they are in sequence (ascending), but many dates are NULL

我想更新 NULL 条目以获得最近的先前日期/时间

I want to update the NULL entries to have the nearest prior date/time

所以第 2 到第 6 行应该从第 1 行和第 1 行获取日期时间8 到 12 应该从 ID 7 等获取日期时间.

So rows 2 thru 6 should get the date time from row ID 1 and 8 thru 12 should get datetime from ID 7, etc.

我确信有一个简单的方法可以通过一个更新语句来做到这一点，但我会画一个空白

I'm sure there's an easy way to do this with a single update statement, but I'd drawing a blank

推荐答案

您应该能够使用相关子查询来做到这一点；当我尝试时，以下查询似乎有效(请参阅下面的小提琴)，但请务必准备好备用以防万一我错了:-)

You should be able to use a correlated subquery to do this; the following query seemed to work when I tried it (see fiddle below), but be sure to have a backup handy in case I'm wrong :-)

update t1
set date = (select max(date) from your_table where id <= t1.id and date is not null)
from your_table t1
where t1.date is null

示例 SQL 小提琴

请注意，如果日期不是我假设的顺序，这可能会产生一些奇怪的结果.

Note that this might give some strange results if the dates aren't sequential which I assumed they are.

此外，如果您使用的是 SQL Server 2012+ 版本，则使用 max() 函数作为窗口函数(max(date) over (...)) 是更好的选择.详细信息在另一个答案中提供.

Also, if you're using a version 2012+ of SQL Server then using the max() function as a windowed function (max(date) over (...)) is a better option. The details are presented in another answer.

这篇关于更新中间行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！