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, a.Val2=case when a.Val2<>0 then a.Val2 else (select top 1 b.Val2 from #G_Goods b where b.ID<a.ID and b.Val2<>0 order by b.ID desc) end, a.Val3=case when a.Val3<>0 then a.Val3 else (select top 1 b.Val3 from #G_Goods b where b.ID<a.ID and b.Val3<>0 order by b.ID desc) end from #G_Goods a -- 结果 select * from #G_Goods/* ID Val1 Val2 Val3 ----------- ----------- ----------- ----------- 1 11250 11260 258 2 11255 11257 74 3 11248 11250 94 4 11248 11254 85 5 11248 11254 88 6 11248 11260 126 7 11258 11262 60 8 11260 11272 60 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 11295 11309 527 17 11294 11308 199 18 11307 11316 73 19 11314 11316 223 20 11331 11316 192 21 11322 11316 219 22 11316 11316 127 23 11315 11319 118 24 11321 11324 86 25 11303 11305 140 26 11294 11301 190 27 11290 11290 157 28 11284 11299 277 29 11284 11284 219 30 11280 11287 238 31 11285 11294 91 32 11291 11302 118(32 行受影响) */
我在"ap0405140"兄弟语句前加了一句,就得到了我想要的结果,下面就是我想要的: update #G_Goods set Val1=0, Val2=0, Val3=0 where Val1=0 or Val2=0or Val3=0 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, a.Val2=case when a.Val2<>0 then a.Val2 else (select top 1 b.Val2 from #G_Goods b where b.ID<a.ID and b.Val2<>0 order by b.ID desc) end, a.Val3=case when a.Val3<>0 then a.Val3 else (select top 1 b.Val3 from #G_Goods b where b.ID<a.ID and b.Val3<>0 order by b.ID desc) end from #G_Goods a -- 结果 select * from #G_Goods
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,
a.Val2=case when a.Val2<>0 then a.Val2
else (select top 1 b.Val2
from #G_Goods b
where b.ID<a.ID and b.Val2<>0
order by b.ID desc) end,
a.Val3=case when a.Val3<>0 then a.Val3
else (select top 1 b.Val3
from #G_Goods b
where b.ID<a.ID and b.Val3<>0
order by b.ID desc) end
from #G_Goods a
-- 结果
select * from #G_Goods/*
ID Val1 Val2 Val3
----------- ----------- ----------- -----------
1 11250 11260 258
2 11255 11257 74
3 11248 11250 94
4 11248 11254 85
5 11248 11254 88
6 11248 11260 126
7 11258 11262 60
8 11260 11272 60
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 11295 11309 527
17 11294 11308 199
18 11307 11316 73
19 11314 11316 223
20 11331 11316 192
21 11322 11316 219
22 11316 11316 127
23 11315 11319 118
24 11321 11324 86
25 11303 11305 140
26 11294 11301 190
27 11290 11290 157
28 11284 11299 277
29 11284 11284 219
30 11280 11287 238
31 11285 11294 91
32 11291 11302 118(32 行受影响)
*/
update #G_Goods set Val1=0, Val2=0, Val3=0 where Val1=0 or Val2=0or Val3=0
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,
a.Val2=case when a.Val2<>0 then a.Val2
else (select top 1 b.Val2
from #G_Goods b
where b.ID<a.ID and b.Val2<>0
order by b.ID desc) end,
a.Val3=case when a.Val3<>0 then a.Val3
else (select top 1 b.Val3
from #G_Goods b
where b.ID<a.ID and b.Val3<>0
order by b.ID desc) end
from #G_Goods a
-- 结果
select * from #G_Goods