create table TestEnvir
(ID smallint not null, Data decimal(4, 0))
insert into TestEnvir
select 1, 0 union all select 2, 1203 union all select 3, 0
union all select 4, 1344 union all select 5, 1988
union all select 6, 0 union all select 7, 1098update a
set data=(select top 1 data from testenvir where id>=a.id and data<>0)
from testenvir a
where data=0select * from testenvirdrop table testenvir/*
ID Data
------ ---------------------------------------
1 1203
2 1203
3 1344
4 1344
5 1988
6 1098
7 1098(7 row(s) affected)
*./
(ID smallint not null, Data decimal(4, 0))
insert into TestEnvir
select 1, 0 union all select 2, 1203 union all select 3, 0
union all select 4, 1344 union all select 5, 1988
union all select 6, 0 union all select 7, 1098update a
set data=(select top 1 data from testenvir where id>=a.id and data<>0)
from testenvir a
where data=0select * from testenvirdrop table testenvir/*
ID Data
------ ---------------------------------------
1 1203
2 1203
3 1344
4 1344
5 1988
6 1098
7 1098(7 row(s) affected)
*./
set Data = (select top 1 B.Data from TestEnvir B where B.ID > TestEnvir.ID AND B.Data <> 0 ORDER BY B.ID)
where TestEnvir.Data = 0
(ID smallint not null, Data decimal(4, 0))
insert into TestEnvir
select 1, 0 union all select 2, 1203 union all select 3, 0
union all select 4, 1344 union all select 5, 1988
union all select 6, 0 union all select 7, 1098select * from TestEnvir
update a
set data=(select max(data) from testenvir where (id=a.id+1 or id=a.id-1) and data<>0)
from testenvir a
where data=0select * from testenvirdrop table testenvir
create table TestEnvir
(ID smallint not null, Data decimal(4, 0))
insert into TestEnvir
select 1, 0 union all select 2, 1203 union all select 3, 0
union all select 4, 1344 union all select 5, 1988
union all select 6, 0 union all select 7, 1098select a.id,b.data from testenvir a,testenvir b
where a.data=0 and a.id=b.id-1
union all
select id,data from testenvir
where data<>0
order by idid data
------ ---------------------------------------
1 1203
2 1203
3 1344
4 1344
5 1988
6 1098
7 1098(7 行受影响)
指出:
1/2楼的问题:
当最后一条记录为0时,则会更新为NULL。3/4楼的问题:
如果记录的ID号不连续,那也会产生NULL记录的。
create table TestEnvir
(ID smallint not null, Data decimal(4, 0))
insert into TestEnvir
select 1, 0 union all select 2, 1203 union all select 3, 0
union all select 4, 1344 union all select 5, 1988
union all select 6, 0 union all select 7, 0
union all select 11, 0
union all select 25, 1098
union all select 28, 0update a
set data=(SELECT MAX(B.data) FROM ((select TOP 1 data from testenvir where id<a.id+1 and data<>0 order by ID DESC)
union (select TOP 1 data from testenvir where id>a.id-1 and data<>0))B)
from testenvir a
where data=0select * from testenvirdrop table testenvir