oracle wm_concat 替换函数,Oracle 10g无法使用listagg函数的替代解决方案[wm_concat]

LISTAGG函数介绍

LISTAGG函数是Oracle 11.2新增的函数，用于字符串拼接，11.2之前的版本无法使用，先来简单介绍一下listagg函数的使用

LISTAGG完整语法

以上是官方文档中给出的完整语法

可以自由选择字符串之间的间隔符号，也可以不要间隔符，直接拼接

WITHIN GROUP及括号里的order by子句是必须有的，即必须按照某个列或表达式的大小顺序排列

OVER关键字是可选，加上则变为分析函数

LISTAGG函数用法示例

数据库版本查看以及示例表数据

sys@PROD1> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

sys@PROD1> conn scott/tiger

Connected.

scott@PROD1> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

7900 JAMES CLERK 7698 03-12月-81 950 30

7902 FORD ANALYST 7566 03-12月-81 3000 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择 14 行。

14 rows selected.

没有分隔符，没有group by子句，按照隐藏列rownum排序(查询时返回结果行的默认顺序)

scott@PROD1> select listagg(ename) within group (order by rownum) from emp;

LISTAGG(ENAME)WITHINGROUP(ORDERBYROWNUM)

---------------------------------------------------------------------------------

SMITHALLENWARDJONESMARTINBLAKECLARKSCOTTKINGTURNERADAMSJAMESFORDMILLER

逗号分隔，按照deptno分组

scott@PROD1> select listagg(ename,',') within group(order by rownum) from emp group by deptno;

LISTAGG(ENAME,',')WITHINGROUP(ORDERBYROWNUM)

---------------------------------------------------------------------

CLARK,KING,MILLER

SMITH,JONES,SCOTT,ADAMS,FORD

ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

用作分析函数(篇幅考虑，拼接字符仅保留了ename的前两个字母)

无partition子句

scott@PROD1> select ename,deptno,listagg(substr(ename,0,2),',') within group(order by rownum) over() as ename_a from emp t;

ENAME DE ENAME_A

------- --- ---------------------

SMITH 20 SM,AL,WA,JO,MA,BL,CL,SC,KI,TU,AD,JA,FO,MI

ALLEN 30 SM,AL,WA,JO,MA,BL,CL,SC,KI,TU,AD,JA,FO,MI

WARD 30 SM,AL,WA,JO,MA,BL,CL,SC,KI,TU,AD,JA,FO,MI

JONES 20 SM,AL,WA,JO,MA,BL,CL,SC,KI,TU,AD,JA,FO,MI

MARTIN 30 SM,AL,WA,JO,MA,BL,CL,SC,KI,TU,AD,JA,FO,MI

BLAKE 30 SM,AL,WA,JO,MA,BL,CL,SC,KI,TU,AD,JA,FO,MI

CLARK 10 SM,AL,WA,JO,MA,BL,CL,SC,KI,TU,AD,JA,FO,MI

SCOTT 20 SM,AL,WA,JO,MA,BL,CL,SC,KI,TU,AD,JA,FO,MI

KING 10 SM,AL,WA,JO,MA,BL,CL,SC,KI,TU,AD,JA,FO,MI

TURNER 30 SM,AL,WA,JO,MA,BL,CL,SC,KI,TU,AD,JA,FO,MI

ADAMS 20 SM,AL,WA,JO,MA,BL,CL,SC,KI,TU,AD,JA,FO,MI

JAMES 30 SM,AL,WA,JO,MA,BL,CL,SC,KI,TU,AD,JA,FO,MI

FORD 20 SM,AL,WA,JO,MA,BL,CL,SC,KI,TU,AD,JA,FO,MI

MILLER 10 SM,AL,WA,JO,MA,BL,CL,SC,KI,TU,AD,JA,FO,MI

已选择 14 行。

有partition子句

scott@PROD1> select ename,deptno,listagg(substr(ename,0,2),',') within group(order by rownum) over(partition by deptno) as ename_a from emp t;

ENAME DE ENAME_A

------- --- ---------------------

CLARK 10 CL,KI,MI

KING 10 CL,KI,MI

MILLER 10 CL,KI,MI

SMITH 20 SM,JO,SC,AD,FO

JONES 20 SM,JO,SC,AD,FO

SCOTT 20 SM,JO,SC,AD,FO

ADAMS 20 SM,JO,SC,AD,FO

FORD 20 SM,JO,SC,AD,FO

ALLEN 30 AL,WA,MA,BL,TU,JA

WARD 30 AL,WA,MA,BL,TU,JA

MARTIN 30 AL,WA,MA,BL,TU,JA

BLAKE 30 AL,WA,MA,BL,TU,JA

TURNER 30 AL,WA,MA,BL,TU,JA

JAMES 30 AL,WA,MA,BL,TU,JA

WM_CONCAT函数介绍

WM_CONCAT函数也可以用作字符串拼接，也是一种聚合函数，也可以用作分析函数，只是函数本身不能像LISTAGG那样自由选择间隔字符，固定使用逗号分隔，但可以用其他函数(例如REPLACE)来实现分隔符的改变。唯一无法实现的是，不能自由选择排序的依据，也就是LISTAGG中必须使用的order by子句。

WM_CONCAT用法示例

没有group by子句

scott@PROD1> select wm_concat(ename) from emp;

WM_CONCAT(ENAME)

--------------------------------------------------------------------------------

SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MIL

使用replace函数去除逗号分隔符

scott@PROD1> select replace(wm_concat(ename),',','') from emp;

REPLACE(WM_CONCAT(ENAME),',','')

--------------------------------------------------------------------------------

SMITHALLENWARDJONESMARTINBLAKECLARKSCOTTKINGTURNERADAMSJAMESFORDMILLER

将逗号替换成竖线

scott@PROD1> select replace(wm_concat(ename),',','') from emp;

REPLACE(WM_CONCAT(ENAME),',','|')

--------------------------------------------------------------------------------

SMITH|ALLEN|WARD|JONES|MARTIN|BLAKE|CLARK|SCOTT|KING|TURNER|ADAMS|JAMES|FORD|MIL

按照deptno分组

scott@PROD1> select deptno,wm_concat(ename) from emp group by deptno;

DEPTNO WM_CONCAT(ENAME)

---------- --------------------------------------------------------------------------------

10 CLARK,MILLER,KING

20 SMITH,FORD,ADAMS,SCOTT,JONES

30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD

验证排序方式

不使用group by子句时

scott@PROD1> select wm_concat(ename||rownum) from emp;

WM_CONCAT(ENAME||ROWNUM)

--------------------------------------------------------------------------------

SMITH1,ALLEN2,WARD3,JONES4,MARTIN5,BLAKE6,CLARK7,SCOTT8,KING9,TURNER10,ADAMS11,JAMES12,FORD13,MILLER14

--由此可见，是按照rownum顺序排列

--但可以在子查询中先排序，再用wm_concat函数实现拼接顺序的改变

select wm_concat(ename||deptno) from (select * from emp order by deptno);

WM_CONCAT(ENAME||DEPTNO)

--------------------------------------------------------------------------------

CLARK10,KING10,MILLER10,JONES20,FORD20,ADAMS20,SMITH20,SCOTT20,WARD30,TURNER30,ALLEN30,JAMES30,BLAKE30,MARTIN30

--按照deptno顺序拼接

使用group by 子句时

scott@PROD1> select wm_concat(ename||rownum) from emp;

WM_CONCAT(ENAME||ROWNUM)

--------------------------------------------------------------------------------

CLARK7,MILLER14,KING9

SMITH1,FORD13,ADAMS11,SCOTT8,JONES4

ALLEN2,JAMES12,TURNER10,BLAKE6,MARTIN5,WARD3

--分组之后，看不出是按照什么顺序排列

--经无论如何在子查询中排序也无法满足分组拼接时组内的拼接顺序的诉求

作分析函数时，用法和LISTAGG一样，这里不再赘述

总结

个人认为，之所以在11.2中新增listagg函数，是为了填补wm_concat函数不能实现组内排序的黑洞，若无分组排序的要求，两者可以通用。