問(wèn)題描述
我有一個(gè)這樣的數(shù)據(jù)集:
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
如您所見(jiàn)，它們是按順序排列的(升序)，但許多日期為 NULL
As you can see, they are in sequence (ascending), but many dates are NULL
我想更新 NULL 條目以獲得最近的先前日期/時(shí)間
I want to update the NULL entries to have the nearest prior date/time
所以第 2 到第 6 行應(yīng)該從第 1 行和第 1 行獲取日期時(shí)間8 到 12 應(yīng)該從 ID 7 等獲取日期時(shí)間.
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.
我確信有一個(gè)簡(jiǎn)單的方法可以通過(guò)一個(gè)更新語(yǔ)句來(lái)做到這一點(diǎn)，但我會(huì)畫(huà)一個(gè)空白
I'm sure there's an easy way to do this with a single update statement, but I'd drawing a blank
推薦答案
您應(yīng)該能夠使用相關(guān)子查詢來(lái)做到這一點(diǎn)；當(dāng)我嘗試時(shí)，以下查詢似乎有效(請(qǐng)參閱下面的小提琴)，但請(qǐng)務(wù)必準(zhǔn)備好備用以防萬(wàn)一我錯(cuò)了:-)
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 小提琴
請(qǐng)注意，如果日期不是我假設(shè)的順序，這可能會(huì)產(chǎn)生一些奇怪的結(jié)果.
Note that this might give some strange results if the dates aren't sequential which I assumed they are.
此外，如果您使用的是 SQL Server 2012+ 版本，則使用 max() 函數(shù)作為窗口函數(shù)(max(date) over (...)) 是更好的選擇.詳細(xì)信息在另一個(gè)答案中提供.
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.
這篇關(guān)于更新中間行的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！