表中NULL的处理?
表结构如下:
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 为主键)
------解决方案--------------------不明白,帮你定 紧挨它是那个数啊.结果不明确呢
------解决方案--------------------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
试试看!
------解决方案--------------------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
------解决方案--------------------用两个游标进行循环进行更新!根据日期排序!
------解决方案--------------------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