您好,登錄后才能下訂單哦!
怎么在postgresql中對string_agg函數(shù)進(jìn)行合并?很多新手對此不是很清楚,為了幫助大家解決這個難題,下面小編將為大家詳細(xì)講解,有這方面需求的人可以來學(xué)習(xí)下,希望你能有所收獲。
1 有時候我們會需要將多條數(shù)據(jù)根據(jù)一些特別的字段做一些合并。比如下面這個查詢,正常會查詢出3條數(shù)據(jù),但是我們會希望根據(jù)create_by 分成兩列顯示
2 這時候需要用到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)用場景(子查詢):
下面的語句是我用來查詢一個學(xué)生在什么時間看了哪些視頻:
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,'分隔符');分隔符可以填寫其他任意的字符,方便后期處理即可;
補充:PostgreSql 聚合函數(shù)string_agg與array_agg,類似mysql中g(shù)roup_concat
string_agg,array_agg 這兩個函數(shù)的功能大同小異,只不過合并數(shù)據(jù)的類型不同。
https://www.postgresql.org/docs/9.6/static/functions-aggregate.html
array_agg(expression)
把表達(dá)式變成一個數(shù)組 一般配合 array_to_string() 函數(shù)使用
string_agg(expression, delimiter)
直接把一個表達(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)
查詢同一個部門下的員工且合并起來
方法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)
看完上述內(nèi)容是否對您有幫助呢?如果還想對相關(guān)知識有進(jìn)一步的了解或閱讀更多相關(guān)文章,請關(guān)注億速云行業(yè)資訊頻道,感謝您對億速云的支持。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。