问题描述
我有一个这样的数据集:
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.
这篇关于更新中间行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！