--> 生成测试数据: @tb DECLARE @tb TABLE (id INT,value VARCHAR(5)) INSERT INTO @tb SELECT 1,'test1' UNION ALL SELECT 2,null UNION ALL SELECT 3,null UNION ALL SELECT 4,null UNION ALL SELECT 5,'test2' UNION ALL SELECT 6,null UNION ALL SELECT 7,null UNION ALL SELECT 8,'test3' UNION ALL SELECT 9,null UNION ALL SELECT 10,null UNION ALL SELECT 11,null UNION ALL SELECT 12,null--SQL查询如下:DECLARE @value VARCHAR(20);UPDATE @tb SET @value = ISNULL(value,@value), value = @value;SELECT * FROM @tb;/* id value ----------- ----- 1 test1 2 test1 3 test1 4 test1 5 test2 6 test2 7 test2 8 test3 9 test3 10 test3 11 test3 12 test3(12 行受影响) */
DECLARE @TB TABLE([id] INT, [value] VARCHAR(5)) INSERT @TB SELECT 1, 'test1' UNION ALL SELECT 2, null UNION ALL SELECT 3, null UNION ALL SELECT 4, null UNION ALL SELECT 5, 'test2' UNION ALL SELECT 6, null UNION ALL SELECT 7, null UNION ALL SELECT 8, 'test3' UNION ALL SELECT 9, null UNION ALL SELECT 10, null UNION ALL SELECT 11, null UNION ALL SELECT 12, nullUPDATE T SET [value]=(SELECT TOP 1 [value] FROM @TB WHERE id<T.id AND [value] IS NOT NULL ORDER BY id DESC) FROM @TB AS T WHERE [value] IS NULLSELECT * FROM @TB /* id value ----------- ----- 1 test1 2 test1 3 test1 4 test1 5 test2 6 test2 7 test2 8 test3 9 test3 10 test3 11 test3 12 test3 */
if object_id('[tab]') is not null drop table [tab] create table [tab]([id] int,[value] varchar(5)) insert [tab] select 1,'test1' union all select 2,null union all select 3,null union all select 4,null union all select 5,'test2' union all select 6,null union all select 7,null union all select 8,'test3' union all select 9,null union all select 10,null union all select 11,null union all select 12,null select id,value=isnull(value,(select top 1 value from tab where t.id>id and value is not null order by id desc)) from [tab] t/* id value ----------- ----- 1 test1 2 test1 3 test1 4 test1 5 test2 6 test2 7 test2 8 test3 9 test3 10 test3 11 test3 12 test3(所影响的行数为 12 行) */ drop table tab
DECLARE @value VARCHAR(20); set @value = '' UPDATE td SET @value = ISNULL(value,@value),value = @value;
update a set value=(select top 1 value from a where id<t.id and value is not null order by id desc) from a t where value is null
declare @t table(id int,value varchar(10)) insert @t values(1 ,'test1') insert @t values(2 ,null) insert @t values(3 ,null) insert @t values(4 ,null) insert @t values(5 ,'test2') insert @t values(6 ,null) insert @t values(7 ,null) insert @t values(8 ,'test3') insert @t values(9 ,null) insert @t values(10 ,null) insert @t values(11 ,null) insert @t values(12 ,null)--更新 update @t set value=(select top 1 value from @t where id<a.id and value is not null order by id desc) from @t a where value is null--查询 select * from @t /* id value ----------- ---------- 1 test1 2 test1 3 test1 4 test1 5 test2 6 test2 7 test2 8 test3 9 test3 10 test3 11 test3 12 test3 */
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-07-03 09:18:41
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (id INT,value VARCHAR(5))
INSERT INTO @tb
SELECT 1,'test1' UNION ALL
SELECT 2,null UNION ALL
SELECT 3,null UNION ALL
SELECT 4,null UNION ALL
SELECT 5,'test2' UNION ALL
SELECT 6,null UNION ALL
SELECT 7,null UNION ALL
SELECT 8,'test3' UNION ALL
SELECT 9,null UNION ALL
SELECT 10,null UNION ALL
SELECT 11,null UNION ALL
SELECT 12,null--SQL查询如下:DECLARE @value VARCHAR(20);UPDATE @tb SET
@value = ISNULL(value,@value),
value = @value;SELECT * FROM @tb;/*
id value
----------- -----
1 test1
2 test1
3 test1
4 test1
5 test2
6 test2
7 test2
8 test3
9 test3
10 test3
11 test3
12 test3(12 行受影响)
*/
INSERT @TB
SELECT 1, 'test1' UNION ALL
SELECT 2, null UNION ALL
SELECT 3, null UNION ALL
SELECT 4, null UNION ALL
SELECT 5, 'test2' UNION ALL
SELECT 6, null UNION ALL
SELECT 7, null UNION ALL
SELECT 8, 'test3' UNION ALL
SELECT 9, null UNION ALL
SELECT 10, null UNION ALL
SELECT 11, null UNION ALL
SELECT 12, nullUPDATE T
SET [value]=(SELECT TOP 1 [value] FROM @TB WHERE id<T.id AND [value] IS NOT NULL ORDER BY id DESC)
FROM @TB AS T
WHERE [value] IS NULLSELECT *
FROM @TB
/*
id value
----------- -----
1 test1
2 test1
3 test1
4 test1
5 test2
6 test2
7 test2
8 test3
9 test3
10 test3
11 test3
12 test3
*/
create table [tab]([id] int,[value] varchar(5))
insert [tab]
select 1,'test1' union all
select 2,null union all
select 3,null union all
select 4,null union all
select 5,'test2' union all
select 6,null union all
select 7,null union all
select 8,'test3' union all
select 9,null union all
select 10,null union all
select 11,null union all
select 12,null
select id,value=isnull(value,(select top 1 value from tab where t.id>id and value is not null order by id desc)) from [tab] t/*
id value
----------- -----
1 test1
2 test1
3 test1
4 test1
5 test2
6 test2
7 test2
8 test3
9 test3
10 test3
11 test3
12 test3(所影响的行数为 12 行)
*/
drop table tab
DECLARE @value VARCHAR(20);
set @value = ''
UPDATE td SET @value = ISNULL(value,@value),value = @value;
update a set value=(select top 1 value from a where id<t.id and value is not null order by id desc) from a t where value is null
insert @t values(1 ,'test1')
insert @t values(2 ,null)
insert @t values(3 ,null)
insert @t values(4 ,null)
insert @t values(5 ,'test2')
insert @t values(6 ,null)
insert @t values(7 ,null)
insert @t values(8 ,'test3')
insert @t values(9 ,null)
insert @t values(10 ,null)
insert @t values(11 ,null)
insert @t values(12 ,null)--更新
update @t set value=(select top 1 value from @t where id<a.id and value is not null order by id desc)
from @t a where value is null--查询
select * from @t
/*
id value
----------- ----------
1 test1
2 test1
3 test1
4 test1
5 test2
6 test2
7 test2
8 test3
9 test3
10 test3
11 test3
12 test3
*/