我想替换表中Val1列含有"0"值的行,用最近一行不含"0"的来替换。如下表中Val1列值在ID是4、5、6、7的值都是"0"值，此四个值都替换为ID为3的值“11248”。 我的数据表,ID列是自增列,第一行也就是ID是1的行是没有"0"值的,"0"值在表中分布没有规律.
表如下:
drop table #G_Goods
CREATE TABLE #G_Goods
( ID [int] IDENTITY(1,1) NOT NULL, Val1 [int], Val2 [int], Val3 [int]);INSERT INTO #G_Goods (Val1, Val2, Val3)
select 11250,11260,258 UNION ALL
select 11255,11257,74 UNION ALL
select 11248,11250,94 UNION ALL
select 0,11254,85 UNION ALL
select 0,0,88 UNION ALL
select 0,11260,126 UNION ALL
select 0,11262,60 UNION ALL
select 11260,11272,0 UNION ALL
select 11267,11282,168 UNION ALL
select 11277,11286,160 UNION ALL
select 0,11292,178 UNION ALL
select 11283,11285,173 UNION ALL
select 11284,11302,100 UNION ALL
select 11289,11292,127 UNION ALL
select 11291,11295,118 UNION ALL
select 0,11309,527 UNION ALL
select 11294,11308,199 UNION ALL
select 11307,11316,73 UNION ALL
select 11314,0,223 UNION ALL
select 11331,0,192 UNION ALL
select 11322,0,219 UNION ALL
select 11316,0,127 UNION ALL
select 11315,11319,118 UNION ALL
select 11321,11324,86 UNION ALL
select 11303,11305,140 UNION ALL
select 11294,11301,190 UNION ALL
select 0,11290,157 UNION ALL
select 0,11299,277 UNION ALL
select 11284,11284,219 UNION ALL
select 11280,11287,238 UNION ALL
select 11285,11294,91 UNION ALL
select 11291,11302,118;
想原表替换后，得到如下的结果：
ID Val1 Val2 Val3
1 11250 11260 258
2 11255 11257 74
3 11248 11250 94
4 11248 11254 85
5 11248 0 88
6 11248 11260 126
7 11248 11262 60
8 11260 11272 0
9 11267 11282 168
10 11277 11286 160
11 11277 11292 178
12 11283 11285 173
13 11284 11302 100
14 11289 11292 127
15 11291 11295 118
16 11291 11309 527
17 11294 11308 199
18 11307 11316 73
19 11314 0 223
20 11331 0 192
21 11322 0 219
22 11316 0 127
23 11315 11319 118
24 11321 11324 86
25 11303 11305 140
26 11294 11301 190
27 11294 11290 157
28 11294 11299 277
29 11284 11284 219
30 11280 11287 238
31 11285 11294 91
32 11291 11302 118
下面是我用其他人提供的查询语句，但是太慢了，我的七万行数据，其中Val1含"0"值有八行，替换八行的用时是45秒。有更快的吗？
update a
set a.Val1=case when a.Val1<>0 then a.Val1
else (select top 1 b.Val1
from #G_Goods b
where b.ID<a.ID and b.Val1<>0
order by b.ID desc) end
from #G_Goods a
------解决思路----------------------
SQL2005+的语法
UPDATE T1
SET Val1=T3.Val1
FROM #G_Goods T1
CROSS APPLY(SELECT TOP 1 Val1 FROM #G_Goods T2 WHERE T1.ID>=T2.ID AND T2.Val1>0 ORDER BY T2.ID DESC)T3
WHERE T1.Val1=0
如果是SQL2000，我想，可以直接加个条件WHERE Val1=0 也会快很多