postgresql regexp_split_to_table函数

最近公司系统在做改造,数据库产品也换了,由Oracle数据库换成postgresql 数据库。

 

在开发的过程中，同事遇到一问题，就是有张表有个字段的数据存储格式是 

A|B|C 这种形式, 现在要专业这种形式 钻石|铂金|黄金。

A对应的是钻石

B对应的是铂金

C对应的是黄金

 

刚开始以为要自定义一个函数,实现这样一个转换功能，偶然在qq群看到 pg有 regexp_split_to_table 函数。于是看是做实验测试:

--建表

drop table if exists test1;
drop table if exists test2;
create table test1(tier_code varchar(100));
create table test2(tier_code varchar(100),tier_name varchar(100));

--测试数据

insert into test1(tier_code) values ('A|B');
insert into test1(tier_code) values('A|B|C');
insert into test1(tier_code) values('A|B|C|E');


insert into test2(tier_code,tier_name) values('A','钻石');
insert into test2(tier_code,tier_name) values('B','铂金');
insert into test2(tier_code,tier_name) values('C','黄金');
insert into test2(tier_code,tier_name) values('E','黑钻');

--查询表的数据

select * from test1;
select * from test2;

--最终查询sql

select t.tier_code, string_agg(t.tier_name, '|')
  from (select t.tier_code,
               t.tier_code_2,
               (select t2.tier_name
                  from test2 t2
                 where t2.tier_code = t.tier_code_2) tier_name
          from (select t.tier_code,
                       regexp_split_to_table(tier_code2, ',') tier_code_2
                  from (select tier_code,
                               replace(tier_code, '|', ',') tier_code2
                          from test1) t) t) t
 group by t.tier_code;

查询结果：

tier_code	string_agg
A|B|C|E	钻石|铂金|黄金|黑钻
A|B	钻石|铂金
A|B|C	钻石|铂金|黄金

 

总结：

1. 上面最终查询sql用到了3个函数，replace函数，将tier_code中的竖线|替换成逗号,;

2. 其实用到了炸裂函数regexp_split_to_table，将一个字段的值根据逗号分隔符拆成一个结果集；

3. 最后用到了string_agg函数把一个结果集合并成一个字符串