ORACLE多行合并为一行(row_number,sys_connect_by_path)

本文介绍了一种使用Oracle数据库的row_number()和sys_connect_by_path函数将多行记录合并为单行记录的方法。该方法适用于需要将同一部门员工姓名合并显示的情况。通过递归查询和层级排序,实现了高效的数据整理。
摘要由CSDN通过智能技术生成

利用Oracle分析函数实现多行数据合并为一行

http://www.cnblogs.com/mlaaalm/archive/2010/05/17/1737607.html

 demo场景,以oracle自带库中的表emp为例:

  select ename,deptno from emp order by deptno;

   

ENAMEDEPTNO
CLARK10
KING10
MILLER10
SMITH20
ADAMS20
FORD20
SCOTT20
JONES20
ALLEN30
BLAKE30
MARTIN30
JAMES30
TURNER30
WARD30

    现在想要将同一部门的人给合并成一行记录,如何做呢?如下:

 

   

ENAMEDEPTNO
CLARK,KING,MILLER10
ADAMS,FORD,JONES,SCOTT,SMITH20
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD30

  通常我们都是自己写函数或在程序中处理,这里我们利用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;

DEPTNOENAMERANK
10CLARK1
10KING2
10MILLER3
20ADAMS1
20FORD2
20JONES3
20SCOTT4
20SMITH5
30ALLEN1
30BLAKE2
30JAMES3
30MARTIN4
30TURNER5
30WARD6

  可看出,经过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;

DEPTNOENAMERANKCURR_LEVELENAME_PATH
10CLARK11CLARK
10KING22CLARK,KING
10MILLER33CLARK,KING,MILLER
10KING21KING
10MILLER32KING,MILLER
10MILLER31MILLER

 

DEPTNOENAMERANKCURR_LEVELENAME_PATH
20ADAMS11ADAMS
20FORD22ADAMS,FORD
20JONES33ADAMS,FORD,JONES
20SCOTT44ADAMS,FORD,JONES,SCOTT
20SMITH55ADAMS,FORD,JONES,SCOTT,SMITH
20FORD21FORD
20JONES32FORD,JONES
20SCOTT43FORD,JONES,SCOTT
20SMITH54FORD,JONES,SCOTT,SMITH
20JONES31JONES
20SCOTT42JONES,SCOTT
20SMITH53JONES,SCOTT,SMITH
20SCOTT41SCOTT
20SMITH52SCOTT,SMITH
20SMITH51SMITH

  这里我们仅列出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);

DEPTNOENAME_PATHENAME_PATH_RANK
10CLARK,KING,MILLER1
10CLARK,KING2
10KING,MILLER3
10CLARK4
10KING5
10MILLER6
DEPTNOENAME_PATHENAME_PATH_RANK
20ADAMS,FORD,JONES,SCOTT,SMITH1
20ADAMS,FORD,JONES,SCOTT2
20FORD,JONES,SCOTT,SMITH3
20ADAMS,FORD,JONES4
20FORD,JONES,SCOTT5
20JONES,SCOTT,SMITH6
20ADAMS,FORD7
20FORD,JONES8
20SCOTT,SMITH9
20JONES,SCOTT10
20ADAMS11
20JONES12
20SMITH13
20SCOTT14
20FORD15

  这里还是仅列出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;

 

ORACLE多行合并为一行。 代码
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        empno,     
                       deptno,
                       ename,
                       rank,
                       
level   as  curr_level,
                       
ltrim (sys_connect_by_path(ename,  ' , ' ),  ' , ' ename_path
                  
from  ( select  deptno,
                               ename,
                               empno,
                               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 ;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值