比如有个表字段a(自增长) 字段 b 字段 c
1 null ddd
2 1111 ffff
3 null ffff
4 222 ffff
5 null fff
6 null fff
7 null fff
如何得到如下结果
2 1111 ffff
3 1111 ffff
4 222 ffff
5 222 fff
6 222 fff
7 222 fff
即:字段b开始null的都删除,如果遇到有数值的,则下面的null用上面的数值替代。遇到新数值,同理更换
1 null ddd
2 1111 ffff
3 null ffff
4 222 ffff
5 null fff
6 null fff
7 null fff
如何得到如下结果
2 1111 ffff
3 1111 ffff
4 222 ffff
5 222 fff
6 222 fff
7 222 fff
即:字段b开始null的都删除,如果遇到有数值的,则下面的null用上面的数值替代。遇到新数值,同理更换
go
create table [tb]([a] int,[b] int,[c] varchar(4))
insert [tb]
select 1,null,'ddd' union all
select 2,1111,'ffff' union all
select 3,null,'ffff' union all
select 4,222,'ffff' union all
select 5,null,'fff' union all
select 6,null,'fff' union all
select 7,null,'fff'
goupdate t
set b=(select top 1 b from tb where a<t.a and b is not null order by a desc)
from tb t
where b is null
go
delete from tb where b is null
goselect * from tb
/**
a b c
----------- ----------- ----
2 1111 ffff
3 1111 ffff
4 222 ffff
5 222 fff
6 222 fff
7 222 fff(6 行受影响)
**/
if object_id('[T]') is not null drop table [T]
go
create table [T]([a] int,[b] int,[c] varchar(4))
insert [T]
select 1,null,'ddd' union all
select 2,1111,'ffff' union all
select 3,null,'ffff' union all
select 4,222,'ffff' union all
select 5,null,'fff' union all
select 6,null,'fff' union all
select 7,null,'fff'
--------------开始查询--------------------------
update a set b=(select top 1 [b] from [T] where a.a>=a and b is not null order by b) from [T] as a
delete from [T] where b is null
select * from [T]
----------------结果----------------------------
/*
a b c
----------- ----------- ----
2 1111 ffff
3 1111 ffff
4 222 ffff
5 222 fff
6 222 fff
7 222 fff(6 行受影响)
*/
(a int, b int, c varchar(6))insert into sh
select 1, null, 'ddd' union all
select 2, 1111, 'ffff' union all
select 3, null, 'ffff' union all
select 4, 222, 'ffff' union all
select 5, null, 'fff' union all
select 6, null, 'fff' union all
select 7, null, 'fff'
-- 建索引
create index ix_sh_a on sh(a)-- 游标方式更新
declare s cursor for select a,b,c from shdeclare @a int,@b int,@c varchar(6),@x int
open s
fetch next from s into @a,@b,@c
while(@@fetch_status<>-1)
begin
if @b is null
update sh set b=@x where a=@a
if @b is not null
select @x=@b
fetch next from s into @a,@b,@c
endclose s
deallocate s-- 结果
select * from sh/*
a b c
----------- ----------- ------
1 NULL ddd
2 1111 ffff
3 1111 ffff
4 222 ffff
5 222 fff
6 222 fff
7 222 fff(7 row(s) affected)
*/