利用Oracle分析函数实现多行数据合并为一行
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;
selectdeptno,ename_path
from(selectdeptno,
ename_path,
row_number()over(partitionbydeptnoorderbydeptno,curr_leveldesc)ename_path_rank
from(
/*
keysubquery,自连接构造树
*/
selectempno,
deptno,
ename,
rank,
levelascurr_level,
ltrim(sys_connect_by_path(ename,","),",")ename_path
from(selectdeptno,
ename,
empno,
row_number()over(partitionbydeptnoorderbydeptno,ename)rank
fromemp
orderbydeptno,ename)
connectbydeptno=priordeptno
andrank-1=priorrank
/*
endquery
*/
))whereename_path_rank=1;
