逗号分割的字典值转字典名称(postgresql)

April 15, 2020

测试表结构

create table dict_muti_name
(
    id       varchar(32),
    save_val text
);

create table dict_keys
(
    dict_key  varchar(200),
    dict_name varchar(200)
)

insert into dict_keys (dict_key, dict_name) values ('big_data','大数据');
insert into dict_keys (dict_key, dict_name) values ('artificial_intelligence','人工智能');
insert into dict_muti_name (id, save_val) VALUES ('1','big_data,artificial_intelligence');

查询sql

select id,
       save_val,
       (
           select array_to_string(
                          array(
                                  select dic.dict_name
                                  from dict_keys dic
                                  where dic.dict_key in (select unnest(string_to_array(save_val, ',')))
                              ),
                          '、')) busi_type_name
from dict_muti_name;

-- 查询结果: 
-- 1    big_data,artificial_intelligence    大数据、人工智能

说明

1). 使用string_to_array,将数据进行拆分

select string_to_array('big_data,artificial_intelligence', ',');
-- {big_data,artificial_intelligence}

2). 使用unnest,将数据转为多列，方便后续操作

select unnest(string_to_array('big_data,artificial_intelligence', ','));
--- 结果
--  big_data
--- artificial_intelligence

3). 反向操作,反向操作多列转为一行，并用逗号分割

select array_to_string(array(
                               (select unnest(string_to_array('big_data,artificial_intelligence', ',')))
                           ), '、');
-- big_data、artificial_intelligence