利用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 |
基本思路:
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 |
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Excel 快速合併多行資料為一行Excel
- Oracle 合併行/多行合併成一行Oracle
- MySQL GROUP_CONCAT函式 將多行資料合併成一行MySql函式
- 使用SQLLOADER將多行資料合併為一行進行載入SQL
- SQL實現多行合併一行 wmsys.wm_concatSQL
- [Hive]用concat_w實現將多行記錄合併成一行Hive
- 【MySQL】MySQL如何合併多行資料,行轉列,group_concat 多行合併MySql
- oracle分析函式Rank, Dense_rank, row_numberOracle函式
- 分析函式rank() row_number函式
- mysql將多行資料合併或按組合並MySql
- 【Analytic】分析函式之ROW_NUMBER函式函式
- Oracle的SYS_CONNECT_BY_PATH函式Oracle函式
- Oracle 中的 ROW_NUMBER() OVER() 分析函式的用法Oracle函式
- oracle 分析函式之(rank()/dense_rank()/row_number())Oracle函式
- ORACLE單行函式與多行函式之七:多行函式之分組函式示例Oracle函式
- oracle下資料的排序分組row_number() over()--分析函式,可用於去重Oracle排序函式
- 利用shell中awk和xargs以及sed將多行多列文字中某一列合併成一行
- orcale 中查詢多行 後合併到一行顯示
- MySQL自定義變數實現row_number分析函式的天坑MySql變數函式
- 多行資料放到一行上
- Oracle多行轉一行Oracle
- ROW_NUMBER() OVER() 分析函式的用法函式
- 分析函式學習3 ROW_NUMBER函式
- 分析函式學習2 SYS_CONNECT_BY_PATH函式
- 用explode函式把json中用一層級資料一行轉換多行函式JSON
- ORACLE單行函式與多行函式之一Oracle函式
- rank,dense_rank,row_number 分析函式函式
- 分析函式row_number()使用一例函式
- 分析函式rank,dense_rank,row_number使用和區別 .函式
- Oracle分析函式RANK(),ROW_NUMBER(),LAG()等的使用方法(轉)Oracle函式
- ORACLE單行函式與多行函式之二:字元函式示例Oracle函式字元
- ORACLE單行函式與多行函式之三:數值函式Oracle函式
- ORACLE單行函式與多行函式之四:日期函式示例Oracle函式
- ORACLE單行函式與多行函式之六:通用函式示例Oracle函式
- 用ORACLE分析函式實現行列轉換Oracle函式
- 轉:SQL Server中將多行資料拼接為一行資料(一個字串)SQLServer字串
- 【分析函式】Oracle分析函式之LAG和LEAD函式Oracle
- 利用樹形查詢實現部分分析函式功能函式