只要行中有"0"值,就用前一起来替换后一行?SQL查询
只要行中有"0"值,就用前一行来替换后一行?SQL查询
我想替换含有"0"值的行,用前一行来替换,我的数据表,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 11258,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 11295,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 11290,11290,157 UNION ALL
select 11284,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 11250 94
5 11248 11250 94
6 11248 11250 94
7 11258 11262 60
8 11258 11262 60
9 11267 11282 168
10 11277 11286 160
11 11277 11286 160
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 11307 11316 73
20 11307 11316 73
21 11307 11316 73
22 11307 11316 73
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
------解决思路----------------------
我想替换含有"0"值的行,用前一行来替换,我的数据表,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 11258,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 11295,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 11290,11290,157 UNION ALL
select 11284,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 11250 94
5 11248 11250 94
6 11248 11250 94
7 11258 11262 60
8 11258 11262 60
9 11267 11282 168
10 11277 11286 160
11 11277 11286 160
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 11307 11316 73
20 11307 11316 73
21 11307 11316 73
22 11307 11316 73
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
------解决思路----------------------
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 行受影响)
*/