一个更新语句

[ttyp]

字段A,每条记录中如果是NULL的话,则其内容更新为上条记录的值(第一条除外)有自动ID

[wzh1215]

update yourtable
set A=(select top 1 a.A from yourtable a where a.id>id order by id)
where A is null

[zjcxc]

update a set 字段A=(select top 1 字段A from 你的表 where id<a.id order by id desc)
from 你的表 a
where 字段A is null

[loverpyh]

update table set A=(select top 1 a.A from yourtable a where a.id<id order by id desc) where A is null

[ttyp]

很奇怪啊,我也是这么写的啊

create table #T (id int,a int)

insert into #t values(1,1)
insert into #t values(2,null)
insert into #t values(3,2)
insert into #t values(4,null)
insert into #t values(5,null)

update #t set a=(select top 1 b.a from #t b where b.id>id order by id) where a is null
select * from #t
drop table #t

[ttyp]

谁来解释下啊~

[zhangzs8896]

update #t
set a=(select top 1 a from #t where id=aa.id-1 order by id)
from #t aa
where a is null

[zhangzs8896]

create table #T (id int,a int)

insert into #t values(1,1)
insert into #t values(2,null)
insert into #t values(3,2)
insert into #t values(4,null)
insert into #t values(5,null)

update #t
set a=(select top 1 a from #t where id=aa.id-1 order by id)
from #t aa
where a is null

select * from #t

drop table #t

--结果

（所影响的行数为 1 行）


（所影响的行数为 1 行）


（所影响的行数为 1 行）


（所影响的行数为 1 行）


（所影响的行数为 1 行）


（所影响的行数为 3 行）

id a
----------- -----------
1 1
2 1
3 2
4 2
5 NULL

（所影响的行数为 5 行）

[ghosthjt]

update yourtable
set A=(select top 1 a.A from yourtable a where a.id>id order by id)
where A is null

[ttyp]

--改为后和楼上一样,可是最后一行仍然为null


create table #T (id int,a int)

insert into #t values(1,1)
insert into #t values(2,null)
insert into #t values(3,2)
insert into #t values(4,null)
insert into #t values(5,null)

--update #t set a=(select top 1 a from #t where id<aa.id order by id) from #t aa where a is null

update aa set a=(select top 1 a from #t where id<aa.id order by id desc)
from #t aa
where a is null


select * from #t

drop table #t

[ttyp]

结果应该是
id a
----------- -----------
1 1
2 1
3 2
4 2
5 2

[ttyp]

--还有就是数据一多也不行,只能更新最近的一条

create table #T (id int,a int)

insert into #t values(1,1)
insert into #t values(2,null)
insert into #t values(3,null)
insert into #t values(4,null)
insert into #t values(5,2)
insert into #t values(6,null)
insert into #t values(7,null)
insert into #t values(8,null)

update aa set a=(select top 1 a from #t where id<aa.id order by id desc)
from #t aa
where a is null


select * from #t

drop table #t


--看来只能用游标做了

[zhangzs8896]

楼主的结果是
id a
----------- -----------
1 1
2 1
3 2
4 2
5 NULL

---你的第5条是null，则取第四条记录的值，因为第四条也是null，显然是上面的结果啊。
你想如果null时，取它上面的不为null的值？

[wzh1215]

如果用：
update aa set a=(select top 1 a from #t where id<aa.id order by id desc)
from #t aa
where a is null
---当第一条'a'的值为NULL时那么更新后所有的记录a都为NULL

[zhangzs8896]

update aa set a=(select top 1 a from #t where id<aa.id and a is not null order by id desc)
from #t aa
where a is null

[ttyp]

对,取以前第一个不为空的值
因为别人建立表是一个EXECL表,名称只填写了一个,以后的就省略了,我想这种应用应该有很多的

[zhangzs8896]

那就使用
update #t
set A=(select top 1 A from #t where id<aa.id and a is not null order by id desc)
from #t aa
where A is null

--测试你的数据如下：
create table #T (id int,a int)
insert into #t values(1,1)
insert into #t values(2,null)
insert into #t values(3,null)
insert into #t values(4,null)
insert into #t values(5,2)
insert into #t values(6,null)
insert into #t values(7,null)
insert into #t values(8,null)

update #t
set A=(select top 1 A from #t where id<aa.id and a is not null order by id desc)
from #t aa
where A is null

select * from #t

drop table #t
--结果：
id a
----------- -----------
1 1
2 1
3 1
4 1
5 2
6 2
7 2
8 2

[1老玉米1]

update table set A=(select top 1 A from table where T.id<id order by id desc)
From Table T
where A is null

[ttyp]

OK
回复人： zhangzs8896(小二) ( ) 信誉：100 2004-07-27 16:01:00 得分: 0
正解