利用Oracle分析函数实现多行数据合并为一行
demo场景 以oracle自带库中的表emp为例
select ename deptno from emp order by deptno;
ENAME DEPTNO CLARK KING MILLER SMITH ADAMS FORD SCOTT JONES ALLEN BLAKE MARTIN JAMES TURNER WARD
现在想要将同一部门的人给合并成一行记录 如何做呢？如下
ENAME DEPTNO CLARK KING MILLER ADAMS FORD JONES SCOTT SMITH ALLEN BLAKE JAMES MARTIN TURNER WARD
通常我们都是自己写函数或在程序中处理 这里我们利用oracle自带的分析函数row_number()和sys_connect_by_path来进行sql语句层面的多行到单行的合并 并且效率会非常高
基本思路
对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 CLARK KING MILLER ADAMS FORD JONES SCOTT SMITH ALLEN BLAKE JAMES MARTIN TURNER WARD 可看出 经过row_number()后 部门人已经按部门和人名进行了排序 并打上了一个位置字段rank
利用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 = prior rank;
各部门递归后的数据量都是 ( +n)/ * n 即 deptno= 数据量 ( + )/ * = ;
deptno= 数据量 ( + )/ * = ; deptno= 数据量 ( + )/ * = ;
DEPTNO ENAME RANK CURR_LEVEL ENAME_PATH CLARK CLARK KING CLARK KING MILLER CLARK KING MILLER KING KING MILLER KING MILLER MILLER MILLER
DEPTNO ENAME RANK CURR_LEVEL ENAME_PATH ADAMS ADAMS FORD ADAMS FORD JONES ADAMS FORD JONES SCOTT ADAMS FORD JONES SCOTT SMITH ADAMS FORD JONES SCOTT SMITH FORD FORD JONES FORD JONES SCOTT FORD JONES SCOTT SMITH FORD JONES SCOTT SMITH JONES JONES SCOTT JONES SCOTT SMITH JONES SCOTT SMITH SCOTT SCOTT SMITH SCOTT SMITH SMITH SMITH
这里我们仅列出deptno= 的 至此我们应该能否发现一些线索了 即每个部门中 curr_level最高的那行 有我们所需要的数据 那后面该怎么办 取出那个数据？ 对了 继续用row_number()进行排位标记 然后再按排位标记取出即可
对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 = prior rank);
DEPTNO ENAME_PATH ENAME_PATH_RANK CLARK KING MILLER CLARK KING KING MILLER CLARK KING MILLER DEPTNO ENAME_PATH ENAME_PATH_RANK ADAMS FORD JONES SCOTT SMITH ADAMS FORD JONES SCOTT FORD JONES SCOTT SMITH ADAMS FORD JONES FORD JONES SCOTT JONES SCOTT SMITH ADAMS FORD FORD JONES SCOTT SMITH JONES SCOTT ADAMS JONES SMITH SCOTT FORD 这里还是仅列出deptno为 的 至此应该很明了了 在进行一次查询 取ename_path_rank为 的即可获得我们想要的结果
获取想要排位的数据 即得部门下所有人多行到单行的合并
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 = prior rank))
lishixinzhi/Article/program/Oracle/201311/17343
