我有一個這樣的示例資料集
Group Age Value
A 1
A 2 0
A 3 20
B 1 0
B 2 0
B 3 25
B 4 0
B 5 0
B 6 50
對于 中的每個不同值Group，替換 null 或零Value。理想的輸出將是
Group Age Value
A 1 20
A 2 20
A 3 20
B 1 25
B 2 25
B 3 25
B 4 25
B 5 50
B 6 50
更新
這似乎可以解決問題
UPDATE test
SET [Value] = nz(DMin(
"[Value]",
"test",
"[Group] = '" & [Group] & "' AND
[Age] < " & [Age] & " AND
[Value] <> 0 AND [Value] IS NOT NULL"
),
DMax(
"[Value]",
"test",
"[Group] = '" & [Group] & "' AND
[Age] > " & [Age] & " AND
[Value] <> 0 AND [Value] IS NOT NULL"
))
WHERE [Value] = 0 OR [Value] IS NULL;
uj5u.com熱心網友回復：
您可以使用它（在 MsAccess SQL 中，列可以從回傳的子查詢或一個值派生 - 或沒有行）：
select
tactl.[Group],
tactl.Age,
( select trepl.[Value]
from test trepl
where tactl.[Group]=trepl.[Group]
and nz(trepl.[Value],0)<>0
and abs(tactl.Age-trepl.Age)=
( select MIN(abs(ta.Age-tb.Age)) as MinDistanceFound
from test ta
inner join
test tb
on (ta.[Group]=tb.[Group] and nz(tb.[Value],0) <> 0)
where (ta.[Group]=tactl.[Group] and ta.[Age]=tactl.[Age])
)
) as [Value]
from test tactl
GROUP并且VALUE可以是大多數 SQL 實作中的保留字（它們在 Access 中）；嘗試為他們使用其他詞（我將它們放在括號中以解決問題）
uj5u.com熱心網友回復：
考慮域聚合，例如DMin為了有一個可更新的查詢：
UPDATE test
SET [Value] = DMin(
"[Value]",
"test",
"[Group] = '" & [Group] & "' AND
[Age] > " & [Age] & " AND
[Value] <> 0 AND [Value] IS NOT NULL"
)
WHERE [Value] = 0 OR [Value] IS NULL;
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/468361.html