数据库

位置:IT落伍者 >> 数据库 >> 浏览文章

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日
 
利用Oracle分析函数实现多行数据合并为一行

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

select enamedeptno from emp order by deptno;

ENAMEDEPTNOCLARKKINGMILLERSMITHADAMSFORDSCOTTJONESALLENBLAKEMARTINJAMESTURNERWARD

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

上一篇:Oracle11G之初体验数据中心自动化等功能

下一篇:数据库考试简介之Oracle认证