create table search.t_test_20200108(
c_id varchar(300),
c_field varchar(300)
)
insert into search.t_test_20200108 values(‘1’,‘医疗器械类,化妆品类,药品类,食品类’);
– concat_ws 第一个参数为符号 以这个符号进行拼接
SELECT
concat_ws(’,’,
(case when c_field like ‘%医疗器械%’ then ‘医疗器械生产’ end),(case when c_field like ‘%化妆品%’ then ‘化妆品生产’ end),(case when c_field like ‘%药品%’ then ‘药品生产’ end),(case when c_field like ‘%食品%’ then ‘食品生产’ end))
as
new_field
FROM
search.t_test_20200108 A
结果: 医疗器械生产,化妆品生产,药品生产,食品生产
– concat 拼接所有参数中的字符串
SELECT
concat(’,’,
(case when c_field like ‘%医疗器械%’ then ‘医疗器械生产’ end),(case when c_field like ‘%化妆品%’ then ‘化妆品生产’ end),(case when c_field like ‘%药品%’ then ‘药品生产’ end),(case when c_field like ‘%食品%’ then ‘食品生产’ end))
as
new_field
FROM
search.t_test_20200108 A
结果:,医疗器械生产化妆品生产药品生产食品生产
– 将一个字段拆分成多个根据自己需要的字段
select
(case when c_field like ‘%医疗器械%’ then ‘医疗器械’ else ‘’ end) as qylx1,
(case when c_field like ‘%化妆品%’ then ‘化妆品’ else ‘’ end) as qylx2
from
search.t_test_20200108 A
WHERE
A.c_id = ‘1’;
create table search.t_test_2020010801(
c_id varchar(300),
c_field varchar(300),
c_name varchar(300)
)
insert into search.t_test_2020010801 values(‘3’,‘食品类’,‘C某’);
– string_agg 把表达式变成一个字符串
select string_agg(c_field,’,’) from search.t_test_2020010801
结果:医疗器械类,化妆品类,食品类
– array_agg(expression) 把表达式变成一个数组 一般配合 array_to_string() 函数使用 跟上述string_agg效果相仿
select array_to_string(array_agg(c_field),’,’) from search.t_test_2020010801
结果:医疗器械类,化妆品类,食品类