postgresql 实现sql多行语句合并一行

多行语句合并一行

三个表关联查询的结果集为：

SELECT users.name,users.age,users.birthday,roles.name FROM users,users_roles,roles WHERE users_roles.userid = users.id and users_roles.roleid = roles.id

name age birthday role
张三 23 1993-1-1 role1
张三 23 1993-1-1 role2
张三 23 1993-1-1 role3

以下语句执行后

SELECT users.name,users.age,users.birthday, array_to_string(ARRAY(SELECT unnest(array_agg(roles.name)) ),',') as roles FROM users,users_roles,roles WHERE users_roles.userid = users.id and users_roles.roleid = roles.id group by users.name,users.age,users.birthday

结果为：

name age birthday roles

张三 23 1993-1-1 role1，role2，role3

补充：PostgreSQL合并查询同一列的多条记录

昨天遇到一个sql问题，需要将一个表中的某一列的多行数据拼接成一行

例如：表中有2列数据：

列1     列2

AAA     企业1

AAA     企业2

AAA     企业3

BBB     企业4

BBB     企业5

我想把这个表变成如下格式：

列1       列2

AAA       企业1，企业2，企业3

BBB       企业4，企业5

类似sqlServer中的stuff函数在PostgreSQL中如何实现呢？

查了一些资料，找到2种比较简单方便的方法，总结一下。

方法一：

SELECT
 列1, string_agg(列2,',') AS 别名
FROM
 表名
GROUP BY 列1 ;

方法二（推荐使用）：

SELECT
  列1, array_to_string(ARRAY(SELECT unnest(array_agg(列2))),',') AS 别名
FROM
  表名
GROUP BY 列1;

如果需要排序后再拼接：

SELECT
  列1, array_to_string(ARRAY(SELECT unnest(array_agg(列2 order by 列2 desc))),',') AS 别名
FROM
  表名
GROUP BY 列1;

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

赞 (0)

相关推荐

  • Postgresql自定义函数详解

    PostgreSQL函数也称为PostgreSQL存储过程. PostgreSQL函数或存储过程是存储在数据库服务器上并可以使用SQL界面调用的一组SQL和过程语句(声明,分配,循环,控制流程等). 语法: CREATE [OR REPLACE] FUNCTION function_name (arguments) RETURNS return_datatype AS $variable_name$ DECLARE declaration; [...] BEGIN < function_body

  • 详解PostgreSql数据库对象信息及应用

    PostgreSql数据库对象主要有数据库.表.视图.索引.schema.函数.触发器等.PostgreSql提供了information_schema schema,其中包括返回数据库对象的视图.如用户有访问权限,可以也在pg_catalog schema中查询表.视图等对象. 1. 查询数据库对象 下面通过示例分别展示如何查询各种数据库对象. 1.1 表查询 PostgreSql 表信息可以从information_schema.tables 或 pg_catalog.pg_tables 视

  • Postgresql排序与limit组合场景性能极限优化详解

    Postgresql排序与limit组合场景性能极限优化详解

    1 构造测试数据 create table tbl(id int, num int, arr int[]); create index idx_tbl_arr on tbl using gin (arr); create or replace function gen_rand_arr() returns int[] as $$ select array(select (1000*random())::int from generate_series(1,64)); $$ language sq

  • PostgreSQL 实现将多行合并转为列

    需求将下列表格相同id的name拼接起来输出成一列 id Name 1 peter 1 lily 2 john 转化后效果: id Name 1 peter:lily 2 john: 实现方式使用 array_to_string 和 array_agg 函数,具体语句如下: string_agg(expression, delimiter) 把表达式变成一个数组 string_agg(expression, delimiter) 直接把一个表达式变成字符串 select id, array_to

  • postgreSQL 非count方法算记录数操作

    一般方法 select count(1) from table_name; 全量扫描一遍表,记录越多,查询速度越慢 新法 PostgreSQL 还真提供了一个这样的途径,那就是系统表 pg_class,这个系统表里头,存储着每个表的统计信息,其中 reltuples 就是对应的表的统计行,统计行的数据是pg有个独立进程,定期扫描不同的表,收集这些表的统计信息,保存在系统表里头. 方法如下: select reltuples::int as total from pg_class where re

  • postgresql通过索引优化查询速度操作

    postgresql通过索引优化查询速度操作

    当数据量比较大的时候,提升查询效率就是需要去考虑的事情了.一个百万级别的表格,如果不做任何优化的话,即使是最简单的查询语句执行起来也是慢的让人难以接受:当然"优化"本身是一个比较复杂的工程,从设计表.字段到查询语句的写法都有很多讲究,这里只考虑索引的方式,且是最普通的索引: 下面的操作中对应数据库表w008_execrise_info(8000数据量), w008_wf02_info(4000数据量) 1 任务表数据 SELECT w.* FROM w008_wf02_info w W

  • PostgreSQL删除更新优化操作

    1. 先说删除吧,因为刚搞了. 删除缓慢的原因:主要是约束的问题.(数据库在有约束的时候,进行操作,会根据约束对相关表进行验证,可想而知,20W的数据验证要耗费多久的时间).其次就是sql的编写.(sql如果查询中包含子查询等的可以优化的where会影响匹配的速度<查询的话就不多逼逼了>).索引的问题 请看下面的 补充部分 具体解决方法: ALTER TABLE tableName DISABLE TRIGGER ALL; delete 目标语句 ALTER TABLE tableName E

  • postgresql 实现sql多行语句合并一行

    多行语句合并一行 三个表关联查询的结果集为: SELECT users.name,users.age,users.birthday,roles.name FROM users,users_roles,roles WHERE users_roles.userid = users.id and users_roles.roleid = roles.id name age birthday role 张三 23 1993-1-1 role1 张三 23 1993-1-1 role2 张三 23 199

  • 使用SQL将多行记录合并成一行实例代码

    使用SQL将多行记录合并成一行实例代码

    目录 前言 1.数据处理前 2,结果数据展示 3,hive处理方式 4,MySQL处理方式 总结 前言 我们在数据开发的过程中,经常会遇到这样的需求,就是将多行合并为一行,并且用特定字符隔开. 1.数据处理前 2,结果数据展示 3,hive处理方式 在hive里面,用concat_ws函数处理 格式: concat_ws(‘分隔符’,collect_set/collect_list(字段)) 参数释义: concat_ws:多行合并一行函数 collect_set:合成数组,数据已去重 coll

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

    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,

  • postgresql 常用SQL语句小结

    目录 1.查询链接数 2.查看死锁状态 3.删除死锁进程 4.备份数据库 5.还原数据库 6.插入数据 插入单条数据 插入多条数据 从一张表查询到的数据插入到另一张表 7 .查询pg中单张表的大小(不包含索引) 8.查询数据库中所有表的大小 9.按顺序查看索引 10 .查询数据库的大小 11.查询被锁定的表 12 .查询一个Schema下面的所有表的总大小(单位MB,包括索引和数据) 14 .查询表大小按大小排序并分离data与index 1.查询链接数 SELECT sum(numbacken

  • SQL Sever查询语句大全集锦

    一. 简单查询 简单的Transact-SQL查询只包括选择列表.FROM子句和WHERE子句.它们分别说明所查询列.查询的 表或视图.以及搜索条件等. 例如,下面的语句查询testtable表中姓名为"张三"的nickname字段和email字段. 代码:SELECT `nickname`,`email`FROM `testtable`WHERE `name`='张三' (一) 选择列表 选择列表(select_list)指出所查询列,它可以是一组列名列表.星号.表达式.变量(包括局

  • postgresql高级应用之行转列&汇总求和的实现思路

    postgresql高级应用之行转列&汇总求和的实现思路

    前言 节前公司业务方需要做一個統計報表,这个报表用于统计当月估计几个明星品的销售情况,而我们的数据是按行存储的就是日期|产品|渠道|销售额这样,说是也奇了怪了,我们买的报(guan)表(yuan)系(la)统(ji) 竟然不能容易地实现...,于是我看了看,然后想了想,发现是可以通过sql算出这样一个报表(多亏了postgresql的高阶函数

  • SQL Server查询语句大全集锦

    一. 简单查询 简单的Transact-SQL查询只包括选择列表.FROM子句和WHERE子句.它们分别说明所查询列.查询的 表或视图.以及搜索条件等. 例如,下面的语句查询testtable表中姓名为"张三"的nickname字段和email字段. 代码:SELECT `nickname`,`email`FROM `testtable`WHERE `name`='张三' (一) 选择列表 选择列表(select_list)指出所查询列,它可以是一组列名列表.星号.表达式.变量(包括局

  • SQL UPDATE 更新语句用法(单列与多列)

    Update 语句 Update 语句用于修改表中的数据. 语法: UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值 例如: Person表: LastName FirstName Address City Gates Bill Xuanwumen 10 Beijing Wilson   Champs-Elysees   更新某一行中的一个列 我们为 lastname 是 "Wilson" 的人添加 firstname: UPDATE Person SET

  • SQL Server 游标语句 声明/打开/循环实例

    SQL Server 游标语句 声明/打开/循环实例

    SQL Server游标语句使用方法: 复制代码 代码如下: --声明一个游标 DECLARE MyCursor CURSOR FOR SELECT TOP 5 FBookName,FBookCoding FROM TBookInfo//定义一个叫MyCursor的游标,存放for select 后的数据 --打开一个游标 OPEN MyCursor//即打开这个数据集 --循环一个游标 DECLARE @BookName nvarchar(2000),@BookCoding nvarchar(

随机推荐