demo场景以oracle自带库中的表emp为例
select enamedeptno from emp order by deptno;
ENAMEDEPTNOCLARKKINGMILLERSMITHADAMSFORDSCOTTJONESALLENBLAKEMARTINJAMESTURNERWARD
现在想要将同一部门的人给合并成一行记录如何做呢？如下
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=;
