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函数不能实现组内排序的黑洞，若无分组排序的要求，两者可以通用。
网友评论