Oracle 列数据聚合方法汇总

网上流传众多Oracle列数据聚合方法，现将各方法整理汇总，以做备忘。

wm_concat

该方法来自wmsys下的wm_concat函数，属于Oracle内部函数，返回值类型varchar2，最大字符数4000。随着版本的变更返回值类型可能会有改动，项目中使用时候最好在新的用户下创建一个函数。使用方法：select deptno,wm_concat（ename） from emp group by deptno;排序方法（未必仅此一种写法）：select *from （select wm_concat（ename） over（partition by deptno order by empno） val,row_number（） over（partition by deptno order by empno desc） rn,a.*from emp a）where rn = 1;如果仅是简单聚合数据，可以使用该函数，优点：效率高。缺点：（1）、返回最大字符数4000；（2）、行数据默认以逗号分隔，可以修改函数更改，但是函数一旦创建不能随意自定义分隔符；（3）、排序实现复杂且效率低；（4）、内部聚合混乱。比如：select wm_concat（col1） col3,wm_concat（col2） col4 from tab;返回的col3和col4里的聚合数据未必是一一对应的。

zh_concat

该函数是在wm_concat基础上修改返回值类型得到，可以返回clob类型数据，内部实现同wm_concat。优缺点同wm_concat。

listagg

11g新增函数，返回值varchar2，同样受4000字符数限制。但是可以排序，可以指定分隔符。使用方法：select deptno,listagg（ename,","） within group（order by empno） from emp group by deptno优点：（1）、可排序（2）、可自定义分隔符缺点：（1）、仅11g之后版本可用（2）、返回最大字符数4000

xmlagg

该方法通过将数据聚合成xml结构，再转换成varchar2或者clob类型。使用方法：select deptno,xmlagg（xmlparse（content ename||"," wellformed） order by empno）.getstringVal（） from emp group by deptno;select deptno,xmlagg（xmlparse（content ename||"," wellformed） order by empno）.getclobval（） from emp group by deptno;优点：（1）、可排序（2）、可返回clob类型容纳大数据量数据（3）、可自定义分隔符（4）、10g可用缺点：（1）、在不排序的情况下效率比wm_concat、zh_concat差（2）、在排序情况下效率比listagg差（3）、最终数据在后面或者前面会多一个分隔符，需要再做处理

sys_connect_by_path

借助connect by实现数据聚合。实现方法：select deptno, resfrom （select rn, level, deptno, sys_connect_by_path（ename, ","） res,connect_by_isleaf ilfrom （select row_number（） over（partition by deptno order by empno） rn,a.*from emp a）start with rn = 1connect by deptno = prior deptnoand prior rn = rn - 1）where il = 1该方法实现复杂，效率低下，这里不再讨论。

总结

不同场景下使用不同方法（最佳选择）：

	10g	11g以上
排序（varchar2）	xmlagg	listagg
排序（clob）	xmlagg	xmlagg
不排序（varchar2）	wm_concat	wm_concat
不排序（clob）	zh_concat	zh_concat

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址