22,199
社区成员
发帖
与我相关
我的任务
分享
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, 1098
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, 0
update 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=0
select * from testenvir
drop 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, 1098
select 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 id
id data
------ ---------------------------------------
1 1203
2 1203
3 1344
4 1344
5 1988
6 1098
7 1098
(7 行受影响)
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, 1098
select * 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=0
select * from testenvir
drop table testenvir
update TestEnvir
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
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, 1098
update a
set data=(select top 1 data from testenvir where id>=a.id and data<>0)
from testenvir a
where data=0
select * from testenvir
drop table testenvir
/*
ID Data
------ ---------------------------------------
1 1203
2 1203
3 1344
4 1344
5 1988
6 1098
7 1098
(7 row(s) affected)
*./