1 有时候我们会需要将多条数据根据一些特别的字段做一些合并。比如下面这个查询，正常会查询出3条数据，但是我们会希望根据create_by 分成两列显示
2 这时候需要用到string_agg函数，先通过group by分组，在进行合并，当然查询结果需要满足group by的限制；sql语句：
|
1 2 |
select create_by,string_agg(videoname, ',' ) as videonames from w008_video_addr_info where id in (4248,538,546) group by create_by |
查询结果：
3 复杂一些的应用场景（子查询）：
下面的语句是我用来查询一个学生在什么时间看了哪些视频：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
select sa.id, info.nickname, ( select string_agg(v.videoname, ',' ) from w008_school_assign_video sv join w008_video_addr_info v on sv.videoaddrinfo =v.id where sv.schoolassignment=sa.id and v.is_removed=0 and sv.is_removed=0 group by v.is_removed) as videos, ( select string_agg(to_char(sv.create_date, 'MM-DD HH24:MI' ), ',' ) from w008_school_assign_video sv join w008_video_addr_info v on sv.videoaddrinfo =v.id where sv.schoolassignment=sa.id and v.is_removed=0 and sv.is_removed=0 group by v.is_removed) as viewtime from w008_school_assignment sa join w008_user_business_info info on sa.userlongid=info.id where sa.shchoolworkid=2514505674916356 |
结果：
当然，string_agg(field,'分隔符');分隔符可以填写其他任意的字符，方便后期处理即可；
补充：PostgreSql 聚合函数string_agg与array_agg，类似mysql中group_concat
string_agg，array_agg 这两个函数的功能大同小异，只不过合并数据的类型不同。
https://www.postgresql.org/docs/9.6/static/functions-aggregate.html
|
1 |
array_agg(expression) |
把表达式变成一个数组 一般配合 array_to_string() 函数使用
|
1 |
string_agg(expression, delimiter) |
直接把一个表达式变成字符串
案例：
|
1 2 3 4 5 6 7 8 9 10 11 |
create table (empno smallint , ename varchar (20), job varchar (20), mgr smallint , hiredate date , sal bigint , comm bigint , deptno smallint ); insert into jinbo.employee(empno,ename,job, mgr, hiredate, sal, comm, deptno) values (7499, 'ALLEN' , 'SALEMAN' , 7698, '2014-11-12' , 16000, 300, 30); insert into jinbo.employee(empno,ename,job, mgr, hiredate, sal, comm, deptno) values (7499, 'ALLEN' , 'SALEMAN' , 7698, '2014-11-12' , 16000, 300, 30); insert into jinbo.employee(empno,ename,job, mgr, hiredate, sal, comm, deptno) values (7654, 'MARTIN' , 'SALEMAN' , 7698, '2016-09-12' , 12000, 1400, 30); select * from jinbo.employee; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+---------+------+------------+-------+------+-------- 7499 | ALLEN | SALEMAN | 7698 | 2014-11-12 | 16000 | 300 | 30 7566 | JONES | MANAGER | 7839 | 2015-12-12 | 32000 | 0 | 20 7654 | MARTIN | SALEMAN | 7698 | 2016-09-12 | 12000 | 1400 | 30 (3 rows ) |
查询同一个部门下的员工且合并起来
方法1：
|
1 2 3 4 5 |
select deptno, string_agg(ename, ',' ) from jinbo.employee group by deptno; deptno | string_agg --------+-------------- 20 | JONES 30 | ALLEN,MARTIN |
方法2：
|
1 2 3 4 5 |
select deptno, array_to_string(array_agg(ename), ',' ) from jinbo.employee group by deptno; deptno | array_to_string --------+----------------- 20 | JONES 30 | ALLEN,MARTIN |
在1条件的基础上，按ename 倒叙合并
|
1 2 3 4 5 |
select deptno, string_agg(ename, ',' order by ename desc ) from jinbo.employee group by deptno; deptno | string_agg --------+-------------- 20 | JONES 30 | MARTIN,ALLEN |
按数组格式输出使用 array_agg
|
1 2 3 4 5 |
select deptno, array_agg(ename) from jinbo.employee group by deptno; deptno | array_agg --------+---------------- 20 | {JONES} 30 | {ALLEN,MARTIN} |
array_agg 去重元素，例如查询所有的部门
|
1 2 3 4 5 6 7 8 9 10 11 |
select array_agg( distinct deptno) from jinbo.employee; array_agg ----------- {20,30} (1 row) #不仅可以去重，还可以排序 select array_agg( distinct deptno order by deptno desc ) from jinbo.employee; array_agg ----------- {30,20} (1 row) |
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。如有错误或未考虑完全的地方，望不吝赐教。
原文链接：https://blog.csdn.net/qxianx/article/details/81985615
查看更多关于postgresql合并string_agg函数的实例的详细内容...