利用Oracle分析函数实现多行数据合并为一行 demo场景,以oracle自带库中的表emp为例:
select ename,deptno from emp order by deptno;
现在想要将同一部门的人给合并成一行记录,如何做呢?如下:
通常我们都是自己写函数或在程序中处理,这里我们利用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;
可看出,经过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;
这里我们仅列出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为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 ( /* key sub query,自连接构造树 */ 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 /* end query */ )) where ename_path_rank = 1;
|
|