t1表结构如下
c1 , c2 ,c3
1 a a
2 b null
3 c c
4 d d
5 e null
现在要更新t1中 c3列为空的字段,更新规则如下:如果某行的c3 列为空 则首先查找比此行的行号稍大且c3列不为空的行的c3列来更新,否则,查找比此行行号稍小的行的c3列来更新。
如上表:
行1 的c3= null,此时 需用 行3 的c3=c去更新 行1 的c3 ;
行5 的c3 = null,此时 需用 行4 的c3去更新 行5的c3
c1 , c2 ,c3
1 a a
2 b null
3 c c
4 d d
5 e null
现在要更新t1中 c3列为空的字段,更新规则如下:如果某行的c3 列为空 则首先查找比此行的行号稍大且c3列不为空的行的c3列来更新,否则,查找比此行行号稍小的行的c3列来更新。
如上表:
行1 的c3= null,此时 需用 行3 的c3=c去更新 行1 的c3 ;
行5 的c3 = null,此时 需用 行4 的c3去更新 行5的c3
declare @t1 table (c1 int,c2 varchar(1),c3 varchar(1))
insert into @t1
select 1,'a','a' union all
select 2,'b',null union all
select 3,'c','c' union all
select 4,'d','d' union all
select 5,'e',nulldeclare @s varchar(20)
select * into #t from @t1 order by c1 desc
update #t set @s=isnull(c3,@s),c3=@s
select * into #t2 from #t order by c1
update #t2 set @s=isnull(c3,@s),c3=@sselect * from #t2
drop table #t
drop table #t2/*
c1 c2 c3
----------- ---- ----
1 a a
2 b c
3 c c
4 d d
5 e d
*/
set c3 = (select top 1 c3 from t1 t where t.c1 > t1.c1 and t.c3 is not null order by t.c1)
where c3 is null
insert into @t1
select 1,'a','a' union all
select 2,'b',null union all
select 3,'c','c' union all
select 4,'d','d' union all
select 5,'e',null
--#1
UPDATE A
SET A.c3 =
CASE
WHEN A.c3 IS NOT NULL THEN A.c3
WHEN EXISTS(SELECT 1 FROM @t1 WHERE c1 > A.c1 AND c3 IS NOT NULL) THEN (SELECT TOP(1) c3 FROM @t1 WHERE c1 > A.c1 AND c3 IS NOT NULL ORDER BY c1)
WHEN EXISTS(SELECT 1 FROM @t1 WHERE c1 < A.c1 AND c3 IS NOT NULL) THEN (SELECT TOP(1) c3 FROM @t1 WHERE c1 < A.c1 AND c3 IS NOT NULL ORDER BY c1 DESC)
END
FROM @t1 A
SELECT * FROM @t1
/*
1 a a
2 b c
3 c c
4 d d
5 e d
*/