欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

PostgreSQL 字符串拆分与合并案例

程序员文章站 2022-07-06 09:54:49
我就废话不多说了,大家还是直接看代码吧~with person_name as ( select c.id, array_to_string(array_agg(distinct p.c_...

我就废话不多说了,大家还是直接看代码吧~

with person_name as (
  select c.id,
      array_to_string(array_agg(distinct p.c_name), ' , ') as p_name
  from biz_notification_config c
       join biz_notification_person p
         on p.id =
           any (string_to_array(c.persons, ',')::int[])
  group by c.id
),
   group_name as (
     select c.id,
        array_to_string(array_agg(distinct g.c_name), ' , ') as g_name
     from biz_notification_config c
         join biz_notification_group g
            on g.id = any (string_to_array(c.c_groups, ',')::int[])
     group by c.id
   )
select config.*, person_name.p_name, group_name.g_name
from biz_notification_config config
     left join person_name
          on config.id = person_name.id
     left join group_name
          on config.id = group_name.id;

array_to_string(array_agg(distinct g.c_name), ' , '):将数组转换为字符串,用“,”分隔。(有点类似于mysql的group_concat()函数)。

array_agg(distinct 想要合并的数据):将想要的数据变成数组。

string_to_array(c.c_groups, ‘,'):将字符串按照“,”分隔成数组。

any (string(varchar)::int[]):将字符串转换为整形。

id = any(list):id的值存在于list中,注意list要和id为同种类型。

补充:postgresql 与mysql 实现分割字符串的方法对比

实现分割字符串。

mysql版本。 由于mysql不支持递归,不支持返回表类型的结果,所以代码比较繁琐。 我用了两个函数以及一个存储过程来实现。

-- 得到分割符的总数。

delimiter $$ 
 
create definer=`root`@`%` function `func_get_split_string_total`(
f_string varchar(1000),f_delimiter varchar(5)
) returns int(11)
begin
 -- get the total number of given string.
 return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
end$$ 
 
delimiter ;

-- 得到具体下表的子字符。

delimiter $$ 
 
create definer=`root`@`%` function `func_get_split_string`(
f_string varchar(1000),f_delimiter varchar(5),f_order int) returns varchar(255) charset utf8
begin
 -- get the separated number of given string.
 declare result varchar(255) default '';
 set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));
 return result;
end$$ 
 
delimiter ;

-- 打印结果。 用临时表来实现。

delimiter $$ 
 
create procedure `sp_print_result`(
 in f_string varchar(1000),in f_delimiter varchar(5)
)
begin
 -- get the separated string.
 declare cnt int default 0;
 declare i int default 0;
 set cnt = func_get_split_string_total(f_string,f_delimiter);
 drop table if exists tmp_print;
 create temporary table tmp_print (v_text varchar(200) not null);
 while i < cnt
 do
  set i = i + 1;
  insert into tmp_print(v_text) values (func_get_split_string(f_string,f_delimiter,i));
 end while;
 select * from tmp_print;
 
end$$ 
 
delimiter ;

我们来执行:

call sp_print_result('love,you,hate,number',',');
query result
v_text 
love 
you 
hate 
number 

postgresql比较灵活, 有以下几种方法来实现。

第一种,普通的分析字符串方法。

 create or replace function split_to_string(
in f_string text, 
in f_delimiter varchar(10)
) returns setof text as
$ytt$
 declare cnt int;
 declare i int;
 declare v_result text;
 begin
   i := 1;
   cnt := length(f_string) - length(replace(f_string,f_delimiter,''))+1;
   while i <= cnt
   loop
    v_result := split_part(f_string,f_delimiter,i);
 return next v_result;
    i := i + 1;
   end loop; 
 
 end;
$ytt$ language plpgsql;

结果:

t_girl=# select split_to_string('love,you,hate,number',',') as result;
 result 
--------
 love
 you
 hate
 number
(4 rows)

第二种, 用自己带的正则函数来实现。

t_girl=# select ytt from regexp_split_to_table('love,you,hate,number', e',+') as ytt;
 ytt  
--------
 love
 you
 hate
 number
(4 rows) 
 
t_girl=# 

第三种,用自带的with 语法来实现。

t_girl=# with recursive ytt(f1,f2) as (
values (0,' '::text) 
union all 
select f1+1,split_part('love,you,hate,number',',',f1+1) from ytt where f1 < 20 
) 
select f2 as result from ytt where f1 >=1 and f1 <= length('love,you,hate,number')-length(replace('love,you,hate,number',',',''))+1;
 result 
--------
 love
 you
 hate
 number
(4 rows)
 
time: 0.742 ms

以上为个人经验,希望能给大家一个参考,也希望大家多多支持。如有错误或未考虑完全的地方,望不吝赐教。