Oracle 列數據聚合方法匯總，oracle聚合匯總

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, 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

該方法實現復雜，效率低下，這裡不再討論。

總結

不同場景下使用不同方法（最佳選擇）：

10g

11g以上

排序（varchar2）

xmlagg

listagg

排序（clob）

xmlagg

xmlagg

不排序（varchar2）

wm_concat

wm_concat

不排序（clob）

zh_concat

zh_concat