Oracle 列数据聚合方法汇总

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

网上流传众多 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, res

from (select rn, level, deptno, sys_connect_by_path(ename, ‘,’) res,

connect_by_isleaf il

from (select row_number() over(partition by deptno order by empno) rn,a.*

from emp a)

start with rn = 1

connect by deptno = prior deptno

and prior rn = rn – 1)

where il = 1

该方法实现复杂，效率低下，这里不再讨论。

总结

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

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-01/139578.htm