表中NULL的处理？

[chenjunjarysky]

表结构如下：
datetime A B C D E F
07/04/03 11:10:00 12 13 null 15 17 20
07/04/03 11:11:00 14 null 18 null 11 null
07/04/03 11:12:00 17 15 17 null 15 23
07/04/03 11:13:00 29 30 31 36 24 17

现在要求当表中某字段为空时，就用紧挨它的一个不为空的数据填补它。
请问各位高手怎么实现啊？(datetime 为主键)

[lin_now]

不明白,帮你定 紧挨它是那个数啊.结果不明确呢

[wmycom]

VB为例：
att = 07/04/03 11:11:00
set rs = server.createObject("adodb.recordset")
rs.open "select * from [表] where datetime="&att,conn,1,3

if trim(rs("B"))<>"" then
else
rs("B") = trim(rs("C"))
end if
rs.update
rs.close
set rs = nothing

试试看！

[chuifengde]

create table tass(da datetime, A int, B int, C int, D int, E int, F int)
insert tass select '07/04/03 11:10:00' ,12 ,13, null, 15, 17, 20
union all select '07/04/03 11:11:00', 14, null, 18, null, 11, null
union all select '07/04/03 11:12:00', 17 ,15 ,17, null, 15, 23
union all select '07/04/03 11:13:00', 29, 30, 31, 36, 24, 17


declare @a varchar(10),@b varchar(10)
declare @v varchar(100)
declare cur cursor for
select name from syscolumns where id=object_id('tass') order by colid
open cur
fetch next from cur into @a
while @@fetch_status=0
begin
fetch next from cur into @b
select @@fetch_status,@a, @b

if @@fetch_status=0 and @a<>'da'
begin
set @v='update tass set '+@b+'='+@a+'+1 from tass where '+@b+' is null'
exec(@v)
select @v
end
set @a=@b
end
close cur
deallocate cur
select * from tass

[chenjunjarysky]

楼上的不好意思，‘最近’的意思是它上面或着是下面不为NULL的数据。并不是左右两边的！

虽然chuifengde(树上的鸟儿) 不合乎我的要求，但还是要谢谢他的！
希望高手来解决啊！

[zsl5305256]

用两个游标进行循环进行更新!根据日期排序!

[free_pop2k]

create table tass(da datetime, A int, B int, C int, D int, E int, F int)
insert tass select '07/04/03 11:10:00' ,12 ,13, null, 15, 17, 20
union all select '07/04/03 11:11:00', 14, null, 18, null, 11, null
union all select '07/04/03 11:12:00', 17 ,15 ,17, null, 15, 23
union all select '07/04/03 11:13:00', 29, 30, 31, 36, 24, 17


--select * from tass

select da,
isnull(A,(select top 1 A from tass where da>a.da and A is not null order by da)) as A,
isnull(B,(select top 1 B from tass where da>a.da and B is not null order by da)) as B,
isnull(C,(select top 1 C from tass where da>a.da and C is not null order by da)) as C,
isnull(D,(select top 1 D from tass where da>a.da and D is not null order by da)) as D,
isnull(E,(select top 1 E from tass where da>a.da and E is not null order by da)) as E,
isnull(F,(select top 1 F from tass where da>a.da and F is not null order by da)) as F
from tass a
order by da

drop table tass