Oracle连接字符串函数listagg()和wmsys.wm_concat()用法简介

共计 1245 个字符，预计需要花费 4 分钟才能阅读完成。

一、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: 字符串连接的结果过长。有兴趣的小伙伴可以研究下解决办法。

：