22,210
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[T]
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 行受影响)
*/
if object_id('[tb]') is not null drop table [tb]
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'
go
update 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
go
select * from tb
/**
a b c
----------- ----------- ----
2 1111 ffff
3 1111 ffff
4 222 ffff
5 222 fff
6 222 fff
7 222 fff
(6 行受影响)
**/
create table sh
(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 sh
declare @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
end
close 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)
*/