PostgreSQL 字符串拆分与合并案例

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

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

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

(0)

相关推荐

  • PostgreSQL批量修改函数拥有者的操作

    Postgresql如何批量修改函数拥有者,默认创建的函数对象的拥有者为当前创建的用户,如果使用postgres超级管理员创建一个test()的函数,拥有者就是postgres用户.下面讲解下如何批量修改拥有者. 本文演示的Postgresql版本如下: PostgreSQL 9.6.8 相关视图 要查询Postgresql的函数和函数参数需要使用函数视图和参数视图,分别记录了函数信息和参数列表信息. 视图一: information_schema.routines 视图routines包含当前

  • postgreSQL的crud操作详解

    postgreSQL学习笔记 欢迎大家指出问题! 版本从6.0开始支持SQL解释器 安装一路下一步. 1.创建数据库 ​ //命令行操作 ​ createdb database_name; ​ CREATE DATABASE database_name; psql -l //展示所有数据库 psql database_name //进入数据库 2.删除数据库 ​ dropdb database; ​ DROP DATABASE database_name; 3.创建表 ​ create tabl

  • PostgreSQL 序列增删改案例

    创建序列 CREATE SEQUENCE if not exists test_mergetable_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 999999999 START 1 CACHE 1; //或者: create sequence if not exists test_mergetable_id_seq increment by 1 minvalue 1 no maxvalue start with 1; 指定序列(给表的主键指定创建好的序列) al

  • postgresql 赋权语句 grant的正确使用说明

    grant select on all tables in schema public to username; 此句是有效的复制语句 PG中有schema的概念, 以下的语句就是不行 网上得来终觉浅,错误比较多,还是要自己试试才行. 补充:postgresql关于访问视图需要的权限 某个用户访问一个视图,这个用户需要具备这个视图的schema的usage和这个视图本身的select权限,如果视图的基表来自其他schema,可能还需要其他schema的usage权限(根据postgresql改造

  • postgresql合并string_agg函数的实例

    1 有时候我们会需要将多条数据根据一些特别的字段做一些合并.比如下面这个查询,正常会查询出3条数据,但是我们会希望根据create_by 分成两列显示 2 这时候需要用到string_agg函数,先通过group by分组,在进行合并,当然查询结果需要满足group by的限制:sql语句: select create_by,string_agg(videoname,',') as videonames from w008_video_addr_info where id in (4248,53

  • PostgreSQL的upsert实例操作(insert on conflict do)

    建表语句: DROP TABLE IF EXISTS "goods"; CREATE TABLE "goods" ( "store_cd" int4 NOT NULL, "good_cd" varchar(50) COLLATE "pg_catalog"."default" NOT NULL, "name" varchar(255) COLLATE "pg_

  • postgresql重置序列起始值的操作

    我就废话不多说了,大家还是直接看代码吧~ -- 序列重置到1000 alter sequence sequence_name restart with 1000 -- 验证 SELECT nextval('sequence_name'); 补充:postgresql重置序列和自增主键 1. 问题背景 数据表中插入了几条测试数据,后又手动删除,导致后面插入数据的时候报主键冲突: ERROR: duplicate key value violates unique constraint "table

  • PostgreSQL 默认权限查看方式

    如何查看PostgreSQL默认权限 当我们对Postgresql的某个用户授予默认权限时, pg_default_acl表存储要被分配给新创建对象的初始权限.你可能通过查询该表获取默认权限,先了解下官方文档的解释: 表 50-17. pg_default_acl的列: 名称 类型 引用 描述 oid oid 行标识符(隐藏属性,必须被显式选择才会显示) defaclrole oid pg_authid.oid 与此项相关的角色的OID defaclnamespace oid pg_namesp

  • PostgreSQL 字符串拆分与合并案例

    我就废话不多说了,大家还是直接看代码吧~ 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

  • SQL Server一个字符串拆分多行显示或者多行数据合并成一个字符串

    概述 STRING_AGG(合并):多行数据合并成一个字符串,以逗号隔开. STRING_SPLIT(拆分):一个字符串,拆分成多行. 一.多行数据合并成一个字符串 1.通过 FOR xml path('') 合并字符串记录 根据name字段,合并code declare @table1 table ( id int ,code varchar(10) , name varchar(20) ); insert into @table1 ( id,code, name ) values ( 1,

  • Pandas实现批量拆分与合并Excel的示例代码

    目录 前言 一.拆分成小表格 二.合并excel 1.介绍 2.代码 前言 提示:这里可以添加本文要记录的大概内容: 将一个EXCEL等份拆成多个EXCEL 将多个小EXCEL合并成一个大EXCEL并标记来源 提示:以下是本篇文章正文内容,下面案例可供参考 一.拆分成小表格 代码如下(示例): import pandas as pd import os work_dir=r"G:\360Downloads\myself\zuoye\合并拆分" splits_dir=f"{wo

  • MySql字符串拆分实现split功能(字段分割转列)

    目录 需求描述 实现的sql 案例演示 字符串拆分: SUBSTRING_INDEX(str, delim, count) 替换函数:replace( str, from_str, to_str) 获取字符串长度:LENGTH( str ) 实现的原理解析 扩展:判断外部值是否在 num列值中 文章参考 需求描述 数据库中 num字段值为: 实现的效果:需要将一行数据变成多行 实现的sql SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,778

  • 分组字符合并SQL语句 按某字段合并字符串之一(简单合并)

    标题:按某字段合并字符串之一(简单合并) 描述:将如下形式的数据按id字段合并value字段. id    value ----- ------ 1     aa 1     bb 2     aaa 2     bbb 2     ccc 需要得到结果: id     value ------ ----------- 1      aa,bb 2      aaa,bbb,ccc 即:group by id, 求 value 的和(字符串相加) 1.sql2000中只能用自定义的函数解决 cr

  • php使用explode()函数将字符串拆分成数组的方法

    本文实例讲述了php使用explode()函数将字符串拆分成数组的方法.分享给大家供大家参考.具体分析如下: explode()函数:字符串拆分成数组 示例代码如下: <?php $str = "朝阳区,海淀区,西城区,东城区,丰台区"; $arr = explode(",",$str); echo "<pre>"; print_r($arr); ?> 结果如下: <pre>Array ( [0] =>

  • asp两组字符串数据比较合并相同数据

    a1="sp2=20;sp1=34;" a2="sp3=2;sp2=3;sp1=4;" 两组字符串数据,将字符串中相同的数据值相加后得到新的一组数据 即"sp3=2;sp2=23;sp1=38" (p.s 一个简单的应用:商品二原有数量20件,商品一原有数量34件,新进货或者新出售了商品二3件,商品一4件等类型模拟情况下计算出进货量,销售量和库存量,小型的进销存系统可采用这样的方法) 那么如何实现两组字符串数据比较合并相同数据? 第一,将两组字符

  • SQL Server实现将特定字符串拆分并进行插入操作的方法

    本文实例讲述了SQL Server实现将特定字符串拆分并进行插入操作的方法.分享给大家供大家参考,具体如下: --循环执行添加操作 declare @idx as int While Len(@UserList) > 0 Begin Set @idx = Charindex(',', @UserList); --只有一条数据 If @idx = 0 and Len(@UserList) > 0 Begin Insert Into BIS_MsgCenterInfo(ID,MsgID,UserI

  • linux命令之目录监控 字符串拆分 监控命令运行结果 统计行数 去除文本头尾空格 获取网卡名浮点数运算等命令使用技巧

    上次为大家介绍了 Linux命令技巧之30个必会的命令技巧,今天再为大家介绍一些比较实用的命令使用技巧 1.监控目录,新创建的文件名追加到日志中 #要安装inotify-tools软件包 #!/bin/bash MON_DIR=/opt inotifywait -mq --format %f -e create $MON_DIR |\ while read files; do echo $files >> test.log done 2.find一次查找多个指定文件类型 # find ./ -

  • Opencv+Python 色彩通道拆分及合并的示例

    一.图像色彩通道拆分 import cv2 img1 = cv2.imread(r"D:\OpencvTest\example.jpg", cv2.IMREAD_COLOR) # 传入一张彩色图片 b, g, r = cv2.split(img1) cv2.imshow("exampleB", b) # 展示B通道图 cv2.imshow("exampleG", g) cv2.imshow("exampleR", r) B通道

随机推荐