大佬教程收集整理的这篇文章主要介绍了ORACLE多行合并为一行,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
SELEct ename,deptno from emp order by deptno;
ename | DEPTNO |
CLARK | 10 |
KING | 10 |
@H_87_27@mILLER | 10 |
SMITH | 20 |
AdamS | 20 |
FORD | 20 |
SCott | 20 |
JOnes | 20 |
ALLEN | 30 |
BLAKE | 30 |
@H_87_27@mARTin | 30 |
JAMES | 30 |
TURNER | 30 |
WARD | 30 |
现在想要将同一部门的人给合并成一行记录,如何做呢?如下:
ename | DEPTNO |
CLARK,KING,MILLER | 10 |
AdamS,FORD,JOnes,SCott,SMITH | 20 |
ALLEN,BLAKE,JAMES,MARTin,TURNER,WARD | 30 |
通常我们都是自己写函数或在程序中处理,这里我们利用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;
@H_674_310@
可看出,经过row_number()后,部门人已经按部门和人名进行了排序,并打上了一个位置字段rank
2、利用Oracle的递归查询connect by进行表内递归,并通过sys_connecT_By_path进行父子数据追溯串的构造,这里要针对ename字段进行构造,使之合并在一个字段内(数据很多,只截取部分)
SELEct deptno,rank,level as curr_level,
ltrim(sys_connecT_By_path(ename,','),') ename_path from (
SELEct 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 | ename | RANK | CURR_LEVEL | ename_PATH |
10 | CLARK | 1 | 1 | CLARK |
10 | KING | 2 | 2 | CLARK,KING |
10 | @H_87_27@mILLER | 3 | 3 | CLARK,MILLER |
10 | KING | 2 | 1 | KING |
10 | @H_87_27@mILLER | 3 | 2 | KING,MILLER |
10 | @H_87_27@mILLER | 3 | 1 | @H_87_27@mILLER |
DEPTNO | ename | RANK | CURR_LEVEL | ename_PATH |
20 | AdamS | 1 | 1 | AdamS |
20 | FORD | 2 | 2 | AdamS,FORD |
20 | JOnes | 3 | 3 | AdamS,JOnes |
20 | SCott | 4 | 4 | AdamS,SCott |
20 | SMITH | 5 | 5 | AdamS,SMITH |
20 | FORD | 2 | 1 | FORD |
20 | JOnes | 3 | 2 | FORD,JOnes |
20 | SCott | 4 | 3 | FORD,SCott |
20 | SMITH | 5 | 4 | FORD,SMITH |
20 | JOnes | 3 | 1 | JOnes |
20 | SCott | 4 | 2 | JOnes,SCott |
20 | SMITH | 5 | 3 | JOnes,SMITH |
20 | SCott | 4 | 1 | SCott |
20 | SMITH | 5 | 2 | SCott,SMITH |
20 | SMITH | 5 | 1 | SMITH |
这里我们仅列出deptno=10、20的,至此我们应该能否发现一些线索了,即每个部门中,curr_level最高的那行,有我们所需要的数据。那后面该怎么办,取出那个数据? 对了,继续用row_number()进行排位标记,然后再按排位标记取出即可。
3、 对deptno继续进行row_number()按curr_level排位
SELEct deptno,ename_path,curr_level desc) ename_path_rank from (SELEct deptno,eName) connect by deptno = prior deptno and rank-1 = prior rank);
DEPTNO | ename_PATH | ename_PATH_RANK |
10 | CLARK,MILLER | 1 |
10 | CLARK,KING | 2 |
10 | KING,MILLER | 3 |
10 | CLARK | 4 |
10 | KING | 5 |
10 | @H_87_27@mILLER | 6 |
DEPTNO | ename_PATH | ename_PATH_RANK |
20 | AdamS,SMITH | 1 |
20 | AdamS,SCott | 2 |
20 | FORD,SMITH | 3 |
20 | AdamS,JOnes | 4 |
20 | FORD,SCott | 5 |
20 | JOnes,SMITH | 6 |
20 | AdamS,FORD | 7 |
20 | FORD,JOnes | 8 |
20 | SCott,SMITH | 9 |
20 | JOnes,SCott | 10 |
20 | AdamS | 11 |
20 | JOnes | 12 |
20 | SMITH | 13 |
20 | SCott | 14 |
20 | FORD | 15 |
这里还是仅列出deptno为10、20的,至此应该很明了了,在进行一次查询,取ename_path_rank为1的即可获得我们想要的结果。
4、获取想要排位的数据,即得部门下所有人多行到单行的合并
SELEct deptno,ename_path from (SELEct deptno,
row_number() over(partition by deptno order by deptno,curr_level desc) ename_path_rank
from (SELEct deptno,eName) connect by deptno = prior deptno and rank-1 = prior rank))
where ename_path_rank=1;
以上是大佬教程为你收集整理的ORACLE多行合并为一行全部内容,希望文章能够帮你解决ORACLE多行合并为一行所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。