Oracle 聚合拼接的常用方式

Oracle常用函数Oracle Database SQL Language Reference, 12c Release 2 (12.2)1 listaggLISTAGGSyntaxDescription of the illustration listagg.eps(listagg_overflow_clause::, order_by_clause::, query_partition_clause::)listagg_overflow_clause::Description of the illustration listagg_overflow_clause.epseg:listagg(t.GROUP_CODE, ,) within group(order by t.GROUP_CODE) as GROUP_CODE2 wm_concat该方式仅Oracle可用。eg:REPLACE(wm_concat(t.GROUP_CODE), ,, ,) AS GROUP_CODE3 xmlaggSyntaxDescription of the illustration xmlagg.epseg: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_ARRAYAGGSyntaxDescription 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.epsJSON_agg_returning_clause::Description of the illustration json_agg_returning_clause.epseg:(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