【Teradata SQL】一个字段为空即取另外一个字段（连续取4个字段）-case when

目标：如果col1为空则取col2的值，如果col2也为空则取col3的值，如果col3还为则取col4的值，如果四个字段均为空则取默认值

1.数据准备

create multiset table test1

(

col_type varchar(20),

col1 varchar(10),

col2 varchar(20),

col3 varchar(30),

col4 varchar(10)

)no primary index;

insert into test1 values ('fetch_col1','col1','col2','col3','clo4'); --字段均有值

insert into test1 values ('fetch_col1','col1','','col3','clo4');--col2无值

insert into test1 values ('fetch_col1','col1','col2','','clo4');--col3无值

insert into test1 values ('fetch_col1','col1','col2','col3','');--col4无值

insert into test1 values ('fetch_col2','','col2','col3','clo4');--col1无值

insert into test1 values ('fetch_col2','','col2','','clo4');--col3无值

insert into test1 values ('fetch_col2','','col2','col3','');--col4无值

insert into test1 values ('fetch_col2','','col2','','');--仅col2有值，其余均无值

insert into test1 values ('fetch_col3','','','col3','clo4');--col1和col2无值

insert into test1 values ('fetch_col3','','','col3','');--仅col3有值，其余均无值

insert into test1 values ('fetch_col4','','','','clo4');--仅col4有值，其余均无值

insert into test1 values ('fetch_default','','','','');--全部无值

2.查询结果

sel

col_type,

case

   when col1 <>'' then col1

   when col2 <> '' then col2

   when col3 <>'' then col3

   when col4 <>'' then col4

   else 'default'

end fetch_value

,col1,col2,col3,col4

from test1 order by 3 desc,4 desc,5 desc,6 desc;

【Teradata SQL】一个字段为空即取另外一个字段（连续取4个字段）-case when的相关教程结束。