求一条sql 记得有人写过，很考验思维的题

[springqingchun]

id jilu
1 0
2 NULL
3 NULL
4 NULL
5 4
6 NULL
7 NULL
8 5
9 NULL

根据id由小到大排列，自动把jilu中为null的值补为上一条记录的值 结果为，求用sql怎么实现
id jilu
1 0
2 0
3 0
4 0
5 4
6 4
7 4
8 5
9 5

[rucypli]

updte tb A
set jilu =(select jilu from tb  where jilu is not null and A.id>id)

[叶子]

declare @t table (id int,jilu int)
insert into @t
select 1,0 union all
select 2,null union all
select 3,null union all
select 4,null union all
select 5,4 union all
select 6,null union all
select 7,null union all
select 8,5 union all
select 9,null

declare @i int;set @i=1
update @t set @i=isnull(jilu,@i),jilu=@i
select * from @t
/*
id          jilu
----------- -----------
1           0
2           0
3           0
4           0
5           4
6           4
7           4
8           5
9           5
*/

[AcHerat]

create table tb(id int,jilu int)
insert into tb
select 1	,0 union all
select 2	,NULL union all
select 3	,NULL union all
select 4	,NULL union all
select 5	,4 union all
select 6	,NULL union all
select 7	,NULL union all
select 8	,5 union all
select 9	,NULL
go

select id,(case when jilu is not null then jilu 
		else (select top 1 jilu from tb where id < t.id and jilu is not null order by id desc) end) jilu
from tb t

drop table tb

/********************

id          jilu
----------- -----------
1           0
2           0
3           0
4           0
5           4
6           4
7           4
8           5
9           5

(9 行受影响)

[springqingchun]

用变量实现

[-晴天]

create table tb(id int,jilu int)
insert into tb select 1,0
insert into tb select 2,NULL
insert into tb select 3,NULL
insert into tb select 4,NULL
insert into tb select 5,4
insert into tb select 6,NULL
insert into tb select 7,NULL
insert into tb select 8,5
insert into tb select 9,NULL
go
select id,case when jilu is null then (select top 1 jilu from tb where id<a.id and jilu is not null order by id desc) else jilu end jilu
from tb a
/*
id          jilu
----------- -----------
1           0
2           0
3           0
4           0
5           4
6           4
7           4
8           5
9           5

(9 行受影响)

*/
go
drop table tb

[cd731107]

create  table t(id int,jilu int)
insert into t
select 1,0 union all
select 2,null union all
select 3,null union all
select 4,null union all
select 5,4 union all
select 6,null union all
select 7,null union all
select 8,5 union all
select 9,null

update t
set jilu =(select top 1 jilu from t as tb where jilu is not null 
            and  id <t.id order by id desc)
where jilu is null

select * from t

/*
id          jilu        
----------- ----------- 
1           0
2           0
3           0
4           0
5           4
6           4
7           4
8           5
9           5

（所影响的行数为 9 行）

*/

[mycodeis0000]

declare @t table (id int,jilu int)
insert into @t
select 1,0 union all
select 2,null union all
select 3,null union all
select 4,null union all
select 5,4 union all
select 6,null union all
select 7,null union all
select 8,5 union all
select 9,null

select * from @t

declare @i int; set @i=1
update @t set @i=isnull(jilu,@i),jilu=@i
select * from @t

+++