string_agg,array_agg 这两个函数的功能大同小异,只不过合并数据的类型不同。
https://www.postgresql.org/docs/9.6/static/functions-aggregate.html
array_agg(expression) 把表达式变成一个数组 一般配合 array_to_string() 函数使用
string_agg(expression,delimiter) 直接把一个表达式变成字符串
案例:
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,deptno) values (7654,'MARTIN','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:
select deptno,string_agg(ename,',') from jinbo.employee group by deptno;
deptno | string_agg
--------+--------------
20 | JONES
30 | ALLEN,MARTIN
方法2:
select deptno,array_to_string(array_agg(ename),') from jinbo.employee group by deptno;
deptno | array_to_string
--------+-----------------
20 | JONES
30 | ALLEN,MARTIN
2、在1条件的基础上,按ename 倒叙合并
select deptno,' order by ename desc) from jinbo.employee group by deptno;
deptno | string_agg
--------+--------------
20 | JONES
30 | MARTIN,ALLEN
3、按数组格式输出使用 array_agg
select deptno,array_agg(ename) from jinbo.employee group by deptno;
deptno | array_agg
--------+----------------
20 | {JONES}
30 | {ALLEN,MARTIN}