看以下测试:
declare @col1 int,@col2 int,@col3 int,@col4 int, @col5 int,@col6 int
select @col1=case when col1 is not null then col1 else @col1 end,
@col2=case when col2 is not null then col2 else @col2 end,
@col3=case when col3 is not null then col3 else @col3 end,
@col4=case when col4 is not null then col4 else @col4 end,
@col5=case when col5 is not null then col5 else @col5 end,
@col6=case when col6 is not null then col6 else @col6 end
from (
select
9 as col1, 8 as col2, 7 as col3, cast(null as int) as col4, 5 as col5, 4 as col6
union all select
3, null, 2 , 1 , 9 , null
union all select
7 , 6 , null, 5 , null, 4
union all select
null, 3 , null, null, null, null
) as tselect @col1 as col1,@col2 as col2,@col3 as col3,@col4 as col4, @col5 as col5,@col6 as col6--结果
col1 col2 col3 col4 col5 col6
----------- ----------- ----------- ----------- ----------- -----------
7 3 2 5 9 4(所影响的行数为 1 行)
declare @col1 int,@col2 int,@col3 int,@col4 int, @col5 int,@col6 int
select @col1=case when col1 is not null then col1 else @col1 end,
@col2=case when col2 is not null then col2 else @col2 end,
@col3=case when col3 is not null then col3 else @col3 end,
@col4=case when col4 is not null then col4 else @col4 end,
@col5=case when col5 is not null then col5 else @col5 end,
@col6=case when col6 is not null then col6 else @col6 end
from (
select
9 as col1, 8 as col2, 7 as col3, cast(null as int) as col4, 5 as col5, 4 as col6
union all select
3, null, 2 , 1 , 9 , null
union all select
7 , 6 , null, 5 , null, 4
union all select
null, 3 , null, null, null, null
) as tselect @col1 as col1,@col2 as col2,@col3 as col3,@col4 as col4, @col5 as col5,@col6 as col6--结果
col1 col2 col3 col4 col5 col6
----------- ----------- ----------- ----------- ----------- -----------
7 3 2 5 9 4(所影响的行数为 1 行)
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货