ENAMEDEPTNOCLARKKINGMILLERADAMSFORDJONESSCOTTSMITHALLENBLAKEJAMESMARTINTURNERWARD 通常我们都是自己写函数或在程序中处理这里我们利用oracle自带的分析函数row_number()和sys_connect_by_path来进行sql语句层面的多行到单行的合并并且效率会非常高 基本思路 对deptno进行row_number()按ename排位并打上排位号 select deptnoenamerow_number() over(partition by deptno order by deptnoename) rank from emp order by deptnoename; DEPTNOENAMERANKCLARKKINGMILLERADAMSFORDJONESSCOTTSMITHALLENBLAKEJAMESMARTINTURNERWARD可看出经过row_number()后部门人已经按部门和人名进行了排序并打上了一个位置字段rank 利用oracle的递归查询connect by进行表内递归并通过sys_connect_by_path进行父子数据追溯串的构造这里要针对ename字段进行构造使之合并在一个字段内(数据很多只截取部分) select deptnoenameranklevel as curr_level ltrim(sys_connect_by_path(ename)) ename_path from ( select deptnoenamerow_number() over(partition by deptno order by deptnoename) rank from emp order by deptnoename) connect by deptno = prior deptno and rank = prior rank; 各部门递归后的数据量都是(+n)/ * n 即deptno= 数据量(+)/ * = ; deptno= 数据量(+)/ * = ; deptno= 数据量(+)/ * = ; DEPTNOENAMERANKCURR_LEVELENAME_PATHCLARKCLARKKINGCLARKKINGMILLERCLARKKINGMILLERKINGKINGMILLERKINGMILLERMILLERMILLER DEPTNOENAMERANKCURR_LEVELENAME_PATHADAMSADAMSFORDADAMSFORDJONESADAMSFORDJONESSCOTTADAMSFORDJONESSCOTTSMITHADAMSFORDJONESSCOTTSMITHFORDFORDJONESFORDJONESSCOTTFORDJONESSCOTTSMITHFORDJONESSCOTTSMITHJONESJONESSCOTTJONESSCOTTSMITHJONESSCOTTSMITHSCOTTSCOTTSMITHSCOTTSMITHSMITHSMITH这里我们仅列出deptno=的至此我们应该能否发现一些线索了即每个部门中curr_level最高的那行有我们所需要的数据那后面该怎么办取出那个数据? 对了继续用row_number()进行排位标记然后再按排位标记取出即可 对deptno继续进行row_number()按curr_level排位 select deptnoename_pathrow_number() over(partition by deptno order by deptnocurr_level desc) ename_path_rank from (select deptnoenameranklevel as curr_level ltrim(sys_connect_by_path(ename)) ename_path from ( select deptnoenamerow_number() over(partition by deptno order by deptnoename) rank from emp order by deptnoename) connect by deptno = prior deptno and rank = prior rank); DEPTNOENAME_PATHENAME_PATH_RANKCLARKKINGMILLERCLARKKINGKINGMILLERCLARKKINGMILLER DEPTNOENAME_PATHENAME_PATH_RANKADAMSFORDJONESSCOTTSMITHADAMSFORDJONESSCOTTFORDJONESSCOTTSMITHADAMSFORDJONESFORDJONESSCOTTJONESSCOTTSMITHADAMSFORDFORDJONESSCOTTSMITHJONESSCOTTADAMSJONESSMITHSCOTTFORD这里还是仅列出deptno为的至此应该很明了了在进行一次查询取ename_path_rank为的即可获得我们想要的结果 获取想要排位的数据即得部门下所有人多行到单行的合并 select deptnoename_path from (select deptnoename_path row_number() over(partition by deptno order by deptnocurr_level desc) ename_path_rank from (select deptnoenameranklevel as curr_level ltrim(sys_connect_by_path(ename)) ename_path from ( select deptnoenamerow_number() over(partition by deptno order by deptnoename) rank from emp order by deptnoename) connect by deptno = prior deptno and rank = prior rank)) where ename_path_rank=;
利用Oracle分析函数实现多行数据合并为一行 |
|
发布日期:2020年05月12日 |
|
demo场景以oracle自带库中的表emp为例 select enamedeptno from emp order by deptno; ENAMEDEPTNOCLARKKINGMILLERSMITHADAMSFORDSCOTTJONESALLENBLAKEMARTINJAMESTURNERWARD 现在想要将同一部门的人给合并成一行记录如何做呢?如下 |
上一篇:Oracle11G之初体验数据中心自动化等功能
下一篇:数据库考试简介之Oracle认证 |
|