1 有時(shí)候我們會(huì)需要將多條數(shù)據(jù)根據(jù)一些特別的字段做一些合并。比如下面這個(gè)查詢，正常會(huì)查詢出3條數(shù)據(jù)，但是我們會(huì)希望根據(jù)create_by 分成兩列顯示
2 這時(shí)候需要用到string_agg函數(shù)，先通過group by分組，在進(jìn)行合并，當(dāng)然查詢結(jié)果需要滿足group by的限制；sql語句：
select create_by,string_agg(videoname,',') as videonames from w008_video_addr_info where id in (4248,538,546)
group by create_by
查詢結(jié)果：
3 復(fù)雜一些的應(yīng)用場景（子查詢）：
下面的語句是我用來查詢一個(gè)學(xué)生在什么時(shí)間看了哪些視頻：
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
結(jié)果：
當(dāng)然，string_agg(field,'分隔符');分隔符可以填寫其他任意的字符，方便后期處理即可；
補(bǔ)充：PostgreSql 聚合函數(shù)string_agg與array_agg，類似mysql中g(shù)roup_concat
string_agg，array_agg 這兩個(gè)函數(shù)的功能大同小異，只不過合并數(shù)據(jù)的類型不同。
https://www.postgresql.org/docs/9.6/static/functions-aggregate.html
把表達(dá)式變成一個(gè)數(shù)組 一般配合 array_to_string() 函數(shù)使用
string_agg(expression, delimiter)
直接把一個(gè)表達(dá)式變成字符串
案例：
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)
查詢同一個(gè)部門下的員工且合并起來
方法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
在1條件的基礎(chǔ)上，按ename 倒敘合并
select deptno, string_agg(ename, ',' order by ename desc) from jinbo.employee group by deptno;
deptno | string_agg
--------+--------------
20 | JONES
30 | MARTIN,ALLEN
按數(shù)組格式輸出使用 array_agg
select deptno, array_agg(ename) from jinbo.employee group by deptno;
deptno | array_agg
--------+----------------
20 | {JONES}
30 | {ALLEN,MARTIN}
array_agg 去重元素，例如查詢所有的部門
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)
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- Postgresql去重函數(shù)distinct的用法說明
- PostgreSQL 定義返回表函數(shù)的操作
- PostgreSQL的generate_series()函數(shù)的用法說明
- PostgreSQL批量修改函數(shù)擁有者的操作
- PostgreSQL數(shù)據(jù)類型格式化函數(shù)操作
- 在postgresql數(shù)據(jù)庫中判斷是否是數(shù)字和日期時(shí)間格式函數(shù)操作
- Postgresql自定義函數(shù)詳解
- postgresql 循環(huán)函數(shù)的簡單實(shí)現(xiàn)操作
