select语句的问题

[xiaomuwing]

表Table1(示例,实际表字段和记录都很多):

id col1 col2
1 1 a
2 null b
3 null c
4 null d
5 2 e
6 null f
7 null g
8 null h
9 null i
10 3 j

要得到的结果是：

id col1 col2
1 1 a
2 1 b
3 1 c
4 1 d
5 2 e
6 2 f
7 2 g
8 2 h
9 2 i
10 3 j

有没有什么比较快的办法?这个数据库很大,我用CURSOR游的太慢了

[子陌红尘]

declare @i int

update table1
set
@i = case when col1=@i then @i else col1 end,
col = @i

[子陌红尘]

create table Table1(id int,col1 int,col2 char(1))
insert into Table1 select 1 ,1 ,'a'
insert into Table1 select 2 ,null,'b'
insert into Table1 select 3 ,null,'c'
insert into Table1 select 4 ,null,'d'
insert into Table1 select 5 ,2 ,'e'
insert into Table1 select 6 ,null,'f'
insert into Table1 select 7 ,null,'g'
insert into Table1 select 8 ,null,'h'
insert into Table1 select 9 ,null,'i'
insert into Table1 select 10,3 ,'j'


declare @i int
update table1
set
@i = (case when col1 is null then @i else col1 end),
col1 = @i


select * from Table1
/*
id col1 col2
1 1 a
2 1 b
3 1 c
4 1 d
5 2 e
6 2 f
7 2 g
8 2 h
9 2 i
10 3 j
*/

drop table Table1

[子陌红尘]

create table Table1(id int,col1 int,col2 char(1))
insert into Table1 select 1 ,1 ,'a'
insert into Table1 select 2 ,null,'b'
insert into Table1 select 3 ,null,'c'
insert into Table1 select 4 ,null,'d'
insert into Table1 select 5 ,2 ,'e'
insert into Table1 select 6 ,null,'f'
insert into Table1 select 7 ,null,'g'
insert into Table1 select 8 ,null,'h'
insert into Table1 select 9 ,null,'i'
insert into Table1 select 10,3 ,'j'


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

select * from Table1
/*
id col1 col2
------------------
1 1 a
2 1 b
3 1 c
4 1 d
5 2 e
6 2 f
7 2 g
8 2 h
9 2 i
10 3 j
*/

drop table Table1

[子陌红尘]

以上两种方法都可以实现，而之前的第一个回复是错的。

[xiaomuwing]

偶像！我拜你为师吧！！！

[zhao606]

to:libin_ftsafe(子陌红尘)
佩服！！！！！！！！