postgresql regexp_split_to_table函数

朱清伟的学习笔记 2020-09-02

1562

最近公司系统在做改造,数据库产品也换了,由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	钻石\|铂金\|黄金

总结：

上面最终查询sql用到了3个函数，replace函数，将tier_code中的竖线|替换成逗号,;
其实用到了炸裂函数regexp_split_to_table，将一个字段的值根据逗号分隔符拆成一个结果集；
最后用到了string_agg函数把一个结果集合并成一个字符串

今天

postgresql

文章转载自朱清伟的学习笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

postgresql regexp_split_to_table函数

评论