問題描述
我有一個這樣的數據集:
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.
這篇關于更新中間行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！