ORACLE多行合并为一行

来源：互联网发布：中小型软件构架实践编辑：程序博客网时间：2024/05/17 07:10

转载地址：http://blog.sina.com.cn/s/blog_497b6d490100rc3r.html

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

　demo场景，以oracle自带库中的表emp为例：

　　select ename,deptno from emp order by deptno;

ENAMEDEPTNOCLARK10KING10MILLER10SMITH20ADAMS20FORD20SCOTT20JONES20ALLEN30BLAKE30MARTIN30JAMES30TURNER30WARD30

现在想要将同一部门的人给合并成一行记录，如何做呢？如下：

ENAMEDEPTNOCLARK,KING,MILLER10ADAMS,FORD,JONES,SCOTT,SMITH20ALLEN,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;

DEPTNOENAMERANK10CLARK110KING210MILLER320ADAMS120FORD220JONES320SCOTT420SMITH530ALLEN130BLAKE230JAMES330MARTIN430TURNER530WARD6
　　可看出，经过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_PATH10CLARK11CLARK10KING22CLARK,KING10MILLER33CLARK,KING,MILLER10KING21KING10MILLER32KING,MILLER10MILLER31MILLER

DEPTNOENAMERANKCURR_LEVELENAME_PATH20ADAMS11ADAMS20FORD22ADAMS,FORD20JONES33ADAMS,FORD,JONES20SCOTT44ADAMS,FORD,JONES,SCOTT20SMITH55ADAMS,FORD,JONES,SCOTT,SMITH20FORD21FORD20JONES32FORD,JONES20SCOTT43FORD,JONES,SCOTT20SMITH54FORD,JONES,SCOTT,SMITH20JONES31JONES20SCOTT42JONES,SCOTT20SMITH53JONES,SCOTT,SMITH20SCOTT41SCOTT20SMITH52SCOTT,SMITH20SMITH51SMITH

　　这里我们仅列出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_RANK10CLARK,KING,MILLER110CLARK,KING210KING,MILLER310CLARK410KING510MILLER6DEPTNOENAME_PATHENAME_PATH_RANK20ADAMS,FORD,JONES,SCOTT,SMITH120ADAMS,FORD,JONES,SCOTT220FORD,JONES,SCOTT,SMITH320ADAMS,FORD,JONES420FORD,JONES,SCOTT520JONES,SCOTT,SMITH620ADAMS,FORD720FORD,JONES820SCOTT,SMITH920JONES,SCOTT1020ADAMS1120JONES1220SMITH1320SCOTT1420FORD15
　　这里还是仅列出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;

代码

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;

—————————————————————————————————————————————————

查询表中的一个字段，返回了多行，就把这么多行的数据都拼成一个字符串。

例：   id  name
       1   aa
       2   bb
       3   cc

  要的结果是"aa,bb,cc"

select WMSYS.WM_CONCAT(a.name) from user a

这样的话，查询出的结果："aa.bb.cc"

中间用点间隔，如果想替换为其他符号，例如用逗分号

select replace(WMSYS.WM_CONCAT(a.name),',','；') from user a

结果："aa;bb;cc"