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

tian1982tian發表於2011-07-17

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;

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/16400082/viewspace-702391/,如需轉載,請註明出處,否則將追究法律責任。

相關文章