利用Oracle分析函式row_number和sys_connect_by_path實現多行資料合併為一行

demo場景，以oracle自帶庫中的表emp為例：

select ename,deptno from emp order by deptno;

ENAME	DEPTNO
CLARK	10
KING	10
MILLER	10
SMITH	20
ADAMS	20
FORD	20
SCOTT	20
JONES	20
ALLEN	30
BLAKE	30
MARTIN	30
JAMES	30
TURNER	30
WARD	30

現在想要將同一部門的人給合併成一行記錄，如何做呢？如下：

ENAME	DEPTNO
CLARK,KING,MILLER	10
ADAMS,FORD,JONES,SCOTT,SMITH	20
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD	30

通常我們都是自己寫函式或在程式中處理，這裡我們利用oracle自帶的分析函式row_number()和sys_connect_by_path來進行sql語句層面的多行到單行的合併，並且效率會非常高。

基本思路：

1、對deptno進行row_number()按ename排位並打上排位號

select deptno,ename,row_number() over(partition by deptno order by deptno,ename) rank
from emp order by deptno,ename;

DEPTNO	ENAME	RANK
10	CLARK	1
10	KING	2
10	MILLER	3
20	ADAMS	1
20	FORD	2
20	JONES	3
20	SCOTT	4
20	SMITH	5
30	ALLEN	1
30	BLAKE	2
30	JAMES	3
30	MARTIN	4
30	TURNER	5
30	WARD	6

可看出，經過row_number()後，部門人已經按部門和人名進行了排序，並打上了一個位置欄位rank

2、利用oracle的遞迴查詢connect by進行表內遞迴，並通過sys_connect_by_path進行父子資料追溯串的構造，這裡要針對ename欄位進行構造，使之合併在一個欄位內（資料很多，只擷取部分）

select deptno,ename,rank,level as curr_level,
ltrim(sys_connect_by_path(ename,','),',') ename_path from (
select deptno,ename,row_number() over(partition by deptno order by deptno,ename) rank
from emp order by deptno,ename) connect by deptno = prior deptno and rank-1 = prior rank;

各部門遞迴後的資料量都是：(1+n)/2 * n 即：deptno=10 資料量：(1+3)/2 * 3 = 6; 

deptno=20 資料量：(1+5)/2 * 5 = 15;      deptno=30 資料量：(1+6)/2 * 6 = 21;  

DEPTNO	ENAME	RANK	CURR_LEVEL	ENAME_PATH
10	CLARK	1	1	CLARK
10	KING	2	2	CLARK,KING
10	MILLER	3	3	CLARK,KING,MILLER
10	KING	2	1	KING
10	MILLER	3	2	KING,MILLER
10	MILLER	3	1	MILLER

 

DEPTNO	ENAME	RANK	CURR_LEVEL	ENAME_PATH
20	ADAMS	1	1	ADAMS
20	FORD	2	2	ADAMS,FORD
20	JONES	3	3	ADAMS,FORD,JONES
20	SCOTT	4	4	ADAMS,FORD,JONES,SCOTT
20	SMITH	5	5	ADAMS,FORD,JONES,SCOTT,SMITH
20	FORD	2	1	FORD
20	JONES	3	2	FORD,JONES
20	SCOTT	4	3	FORD,JONES,SCOTT
20	SMITH	5	4	FORD,JONES,SCOTT,SMITH
20	JONES	3	1	JONES
20	SCOTT	4	2	JONES,SCOTT
20	SMITH	5	3	JONES,SCOTT,SMITH
20	SCOTT	4	1	SCOTT
20	SMITH	5	2	SCOTT,SMITH
20	SMITH	5	1	SMITH

 

這裡我們僅列出deptno=10、20的，至此我們應該能否發現一些線索了，即每個部門中，curr_level最高的那行，有我們所需要的資料。那後面該怎麼辦，取出那個資料？ 對了，繼續用row_number()進行排位標記，然後再按排位標記取出即可。

3、 對deptno繼續進行row_number()按curr_level排位

 select deptno,ename_path,row_number() over(partition by deptno order by deptno,curr_level desc) ename_path_rank from (select deptno,ename,rank,level as curr_level,
ltrim(sys_connect_by_path(ename,','),',') ename_path from (
select deptno,ename,row_number() over(partition by deptno order by deptno,ename) rank
from emp order by deptno,ename) connect by deptno = prior deptno and rank-1 = prior rank);

DEPTNO	ENAME_PATH	ENAME_PATH_RANK
10	CLARK,KING,MILLER	1
10	CLARK,KING	2
10	KING,MILLER	3
10	CLARK	4
10	KING	5
10	MILLER	6

DEPTNO	ENAME_PATH	ENAME_PATH_RANK
20	ADAMS,FORD,JONES,SCOTT,SMITH	1
20	ADAMS,FORD,JONES,SCOTT	2
20	FORD,JONES,SCOTT,SMITH	3
20	ADAMS,FORD,JONES	4
20	FORD,JONES,SCOTT	5
20	JONES,SCOTT,SMITH	6
20	ADAMS,FORD	7
20	FORD,JONES	8
20	SCOTT,SMITH	9
20	JONES,SCOTT	10
20	ADAMS	11
20	JONES	12
20	SMITH	13
20	SCOTT	14
20	FORD	15

這裡還是僅列出deptno為10、20的，至此應該很明瞭了，在進行一次查詢，取ename_path_rank為1的即可獲得我們想要的結果。

4、獲取想要排位的資料，即得部門下所有人多行到單行的合併

select deptno,ename_path from (select deptno,ename_path,
row_number() over(partition by deptno order by deptno,curr_level desc) ename_path_rank
from (select deptno,ename,rank,level as curr_level,
ltrim(sys_connect_by_path(ename,','),',') ename_path from (
select deptno,ename,row_number() over(partition by deptno order by deptno,ename) rank
from emp order by deptno,ename) connect by deptno = prior deptno and rank-1 = prior rank))
where ename_path_rank=1;