Oracle 聚合拼接的常用方式

Oracle常用函数：Oracle Database SQL Language Reference, 12c Release 2 (12.2)

1 listagg

LISTAGG

Syntax

Description of the illustration listagg.eps

(listagg_overflow_clause::=, order_by_clause::=, query_partition_clause::=)

listagg_overflow_clause::=

Description of the illustration listagg_overflow_clause.eps

eg:

listagg(t.GROUP_CODE, '","') within group(order by t.GROUP_CODE) as GROUP_CODE

2 wm_concat

该方式仅Oracle可用。

eg:

REPLACE(wm_concat(t.GROUP_CODE), ',', '","') AS GROUP_CODE

3 xmlagg

Syntax

Description of the illustration xmlagg.eps

eg:

xmlagg(xmlelement(e,t.GROUP_CODE,'","').extract('//text()')).getclobval() AS GROUP_CODE

--查询获取指定用户对象涉及的表空间DDL SELECT REGEXP_REPLACE(REGEXP_REPLACE(XMLAGG(XMLELEMENT(E, T.TABLESPACEDDL, ';').EXTRACT('//text()')) .GETCLOBVAL(), CHR(38) || 'quot;', '"'), CHR(38) || 'apos;', CHR(39)) AS TABLESPACEDDL FROM (SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TABLESPACE_NAME) TABLESPACEDDL FROM (SELECT DISTINCT A.TABLESPACE_NAME TABLESPACE_NAME FROM DBA_SEGMENTS A WHERE A.TABLESPACE_NAME NOT IN ('SYS', 'SYSTEM') AND A.OWNER IN ('A', 'B', 'C', 'XXX'))) T;

4 JSON_ARRAYAGG

Syntax

Description of the illustration json_arrayagg.eps

(See order_by_clause::= in the documentation onSELECTfor the syntax of this clause)

JSON_on_null_clause::=

Description of the illustration json_on_null_clause.eps

JSON_agg_returning_clause::=

Description of the illustration json_agg_returning_clause.eps

eg:

(1)json_value(JSON_ARRAYAGG(json_value(json_object(t.GROUP_CODE),'$.GROUP_CODE') returning clob) ,'$[0]') AS GROUP_CODE

(2)rtrim(ltrim(JSON_ARRAYAGG(json_value(json_object(t.GROUP_CODE),'$.GROUP_CODE') returning clob),'["'),'"]') AS GROUP_CODE