oracle中listagg()和wmsys.wm_concat()基本用法

一、LISTAGG() 簡介

介紹：其函式在Oracle 11g 版本中推出，對分組後的資料按照一定的排序進行字串連線。

其中，“[,]”表示字串連線的分隔符，如果選擇使用[over (partition by )]則會使其變成分析函式；

用法1：

SELECT aaa, listagg(bbb,',') within GROUP (ORDER BY aaa) FROM table  GROUP BY aaa

用法2：

SELECT aaa, listagg(bbb,',') within GROUP (ORDER BY aaa) over(partition by aaa) FROM table

二、wm_concat()簡介

介紹：其函式在Oracle 10g推出，在10g版本中，返回字串型別，在11g版本中返回clob型別。

括號裡面的引數是列，而且可以是多個列的集合，也就是說在括號裡面可以自由地用‘||’合併字串。

用法1：

Select aaa, wmsys.wm_concat(bbb || '(' || ccc || ')' ) from table group by aaa

用法2：

Select aaa, wmsys.wm_concat(bbb || '(' || ccc || ')' ) over(partition by aaa) from table 

三、應用例項：

3.1、建立表

CREATE TABLE TESTAGG
(　　

　　A VARCHAR2(20),

　　B VARCHAR2(20),

　　C VARCHAR2(20)
)

3.2、初始化資料

INSERT INTO "TESTAGG" (A, B, C) VALUES ('1', 'B1','C1')

INSERT INTO "TESTAGG" (A, B, C) VALUES ('1', 'B2','C2')

INSERT INTO "TESTAGG" (A, B, C) VALUES ('1', 'B3','C3')

INSERT INTO "TESTAGG" (A, B, C) VALUES ('2', 'B4','C4')

INSERT INTO "TESTAGG" (A, B, C) VALUES ('2', 'B5','C5')

INSERT INTO "TESTAGG" (A, B, C) VALUES ('3', 'B6','C6')

3.3、wm_concat()用法

select a,wm_concat(b|| '(' || c || ')') as bc from testagg group by a order by a

1  <CLOB>--B1(C1),B2(C2),B3(C3)

2  <CLOB>--B4(C4),B5(C5)

3  <CLOB>--B6(C6)

若要轉化成string可以用to_char()函式。

3.4、 LISTAGG()用法

select a,LISTAGG(b,',' ) within group(order by a) as bc from testagg group by a

1 B1,B2,B3

2 B4,B5

3 B6

注意：但當資料量比較大時，一般clob欄位超過4000，卻報ORA-01489:字串連線的結果過長。有興趣的小夥伴可以研究下解決辦法。