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;
以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。如有错误或未考虑完全的地方,望不吝赐教。
相关推荐
-
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组合场景性能极限优化详解
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通过索引优化查询速度操作
当数据量比较大的时候,提升查询效率就是需要去考虑的事情了.一个百万级别的表格,如果不做任何优化的话,即使是最简单的查询语句执行起来也是慢的让人难以接受:当然"优化"本身是一个比较复杂的工程,从设计表.字段到查询语句的写法都有很多讲究,这里只考虑索引的方式,且是最普通的索引: 下面的操作中对应数据库表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将多行记录合并成一行实例代码
目录 前言 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一个字符串拆分多行显示或者多行数据合并成一个字符串
概述 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高级应用之行转列&汇总求和的实现思路
前言 节前公司业务方需要做一個統計報表,这个报表用于统计当月估计几个明星品的销售情况,而我们的数据是按行存储的就是日期|产品|渠道|销售额这样,说是也奇了怪了,我们买的报(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游标语句使用方法: 复制代码 代码如下: --声明一个游标 DECLARE MyCursor CURSOR FOR SELECT TOP 5 FBookName,FBookCoding FROM TBookInfo//定义一个叫MyCursor的游标,存放for select 后的数据 --打开一个游标 OPEN MyCursor//即打开这个数据集 --循环一个游标 DECLARE @BookName nvarchar(2000),@BookCoding nvarchar(
随机推荐
- JDBC-ODBC翻页例子
- Mongodb如何开启用户访问控制详解
- 基于jquery实现图片相关操作(重绘、获取尺寸、调整大小、缩放)
- React组件之间的通信的实例代码
- jQuery操作DOM之获取表单控件的值
- php实现用户注册密码的crypt加密
- 基于Python3 逗号代码 和 字符图网格(详谈)
- PHP+Ajax异步带进度条上传文件实例
- mysql源码安装脚本分享
- 关于Java企业级项目开发思想
- java方法重写实例分析
- python获取当前运行函数名称的方法实例代码
- JavaScript中数组对象的那些自带方法介绍
- Android编程自定义对话框(Dialog)位置及大小的方法
- 在CentOS6上安装Python2.7的解决方法
- Spring Boot Maven 打包可执行Jar文件的实现方法
- 更优雅的微信小程序骨架屏实现详解
- python实现多层感知器MLP(基于双月数据集)
- golang基础之Gocurrency并发
- mongodb中非常好用的Aggregate入门教程