如果本列数据为空,如何去取上列不为空的值
如果本列数据为空,如何去取上列不为空的值
例:
表a
A B C D
2 3 NULL 4
1 NULL 3 2
得到结果
A B C D
2 3 3 4
1 1 3 2
[解决办法]
--动态的
create table test(A int,B int,C int,D int)
insert into test
select 2,3,NULL,4
union all select 1,NULL,3,2
union all select null,5,null,20
go
declare @s varchar(1000)
select @s=''
select @s=@s+',['+a.name+']=case when ['+a.name+'] is null then ['+b.name+'] else ['+a.name+'] end'
from syscolumns a
left join syscolumns b on a.colid=b.colid+1
where a.id=OBJECT_ID('test') and b.id=OBJECT_ID('test')
order by a.colid
exec ('select A'+@s+' from test')
drop table test
/*
ABCD
2334
1132
NULL5520 -->如果第一列为空，那只能取原值 了
*/
if object_id('tb') is not null
drop table tb
go
create table tb(A int,B int,C int,D int)
insert into tb
select 2,3,NULL,4
union all select 1,NULL,3,2
union all select null,5,null,20
go
declare @sql varchar(1000)
select @sql=''
select @sql=@sql+',case when ['+d.name
+'] is null then '+c.name+' else ['+d.name+'] end as '+d.name
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
inner join sys.columns d
on d.column_id = c.column_id + 1
and t.object_id = d.object_id
where t.name = 'tb'
order by c.column_id
set @sql ='select A'+ @sql +' from tb'
--输出动态语句
select @sql
/*
select A,
case when [B] is null then A else [B] end as B,
case when [C] is null then B else [C] end as C,
case when [D] is null then C else [D] end as D
from tb
*/
exec (@sql)
/*
A BCD
2 334
1 132
NULL 5520
*/
