Oracle   发布时间:2022-05-17  发布网站:大佬教程  code.js-code.com
大佬教程收集整理的这篇文章主要介绍了ORACLE多行合并为一行大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。

 demo场景,以Oracle自带库中的表emp为例:

  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@ DEPTNO ename RANK 10 CLARK 1 10 KING 2 10 @H_87_27@mILLER 3 20 AdamS 1 20 FORD 2 20 JOnes 3 20 SCott 4 20 SMITH 5 30 ALLEN 1 30 BLAKE 2 30 JAMES 3 30 @H_87_27@mARTin 4 30 TURNER 5 30 WARD 6


  可看出,经过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;

代码
SELEctdeptno,ename_path
from(SELEctdeptno,
ename_path,
row_number()
over(partitionbydeptnoorderbydeptno,curr_leveldesc)ename_path_rank
from(


SELEctempno,
deptno,
ename,
rank,
levelascurr_level,
ltrim(sys_connecT_By_path(ename,','),')ename_path
from(SELEctdeptno,
empno,eName)rank
fromemp
orderbydeptno,eName)
connect
bydeptno=priordeptno
andrank-1=priorrank

)) 
whereename_path_rank=1;
@H_502_1874@

—————————————————————————————————————————————————
@H_502_1874@
查询表中的一个字段,返回了多行,就把这么多行的数据都拼成一个字符串。

例:IDname
1aa
2bb
3cc

要的结果是"aa,bb,cc"
@H_502_1874@
SELEct WMSYs.WM_CONCAT(a.Name) from user a这样的话,查询出的结果:"aa.bb.cc"中间用点间隔,如果想替换为其他符号,例如用逗分号SELEct replace(WMSYs.WM_CONCAT(a.Name),';') from user a结果:"aa;bb;cc" @H_502_1874@ @H_502_1874@ @H_502_1874@
本图文内容来源于网友网络收集整理提供,作为学习参使用,版权属于原作者。
编程之家官方1群(满)
编程之家官方2群(满)
编程之家官方3群(满)
编程之家官方4群
编程之家官方5群(新)
  • 上一篇:【读书笔记】OracLE 内存管理下一篇:OracleDBConsoleorcl 无法启动解决

Oracle相关文章

Oracle导入导出命令
Oracle递归查询
SQL: SELEct * from PT_ORG_INFO START WITH id='102' CONNECT BY PRIOR id=par_id 连表递归查询SQL: sel
DG:重启之后主备数据重新同步
问题描述:本来配置好的DG第二天重启之后,发现主备库数据不能同步,在主库上执行日志切换以及创建表操作都传不到备库上,造成这种错误的原因是主库实例断掉后造成备库日志与主库无法实时接收 主库:orcl 备
delete误删数据使用SCN恢复
51CTO博客 问题描述:使用scn号恢复误删数据 1.查询系统闪回的scn值以及当前日志的scn值,因为我这个是测试,创建的表是在在后边,所以scn值要大于下边这两个scn值,所以对我恢复数据没
ORA-00845 startup启动不起来关于磁盘空间扩充
问题描述:今天在虚拟机下进行startup的操作,但是没有起来,系统报错:ORA-00845: MEMORY_TARGET not supported on this system 1.startup
Oracle数据库实例启动过程分三个步骤,分别是启动实例加载数据库,打开数据。 1.NOMOUNT模式:这种模式只会创建实例,不会打开任何的数据文件,用户要以sysdba的身份登录,才具有关闭和启动
startup启动不起来关于监听的问题
问题描述:要在SQLPlus中启动到startup状态,但是提示我没有监听,本来以为启动一下就可以,但是connecTing to一直卡半天,stop都停止不了 1.发现监听有问题,前去更改 SQL&
DG:switchover切换操作
问题描述:我们配置DG的目的就是为了在主库出现故障时,备库能够提供服务,保证业务的正常运行,switchover是用户有计划的进行停机切换,能够保证不丢失数据,我记录一下我进行switchover中的
@H_948_2050@mySQLMsSQLOraclesqlitePostgre SQLR_600_11845@ariadbMongoDBNoSQLHBaseJDBCmycat
  • Oracle导入导出命令
  • Oracle递归查询
  • Oracle@R_343_10589@、表空间并设置
  • • DG:重启之后主备数据重新同步
  • delete误删数据使用SCN恢复
  • Oracle逻辑存储结构
  • • ORA-00845 startup启动不起来关
  • oracle实例状态
  • • 基于SCN增量恢复DG同步
  • • startup启动不起来关于监听的问
  • • DG:switchover切换操作
  • • RHel5.6静默安装Oracle11.2.0数

ORACLE多行合并为一行

微信公众号搜 "程序精选"关注
  • Oracle导入导出命令
  • Oracle递归查询
  • Oracle@R_343_10589@、表空间并设置
  • • DG:重启之后主备数据重新同步
  • delete误删数据使用SCN恢复
  • Oracle逻辑存储结构
  • • ORA-00845 startup启动不起来关
  • oracle实例状态
  • • 基于SCN增量恢复DG同步
  • • startup启动不起来关于监听的问
python-3.xpandasswiftarraysspring-boosql-serverrubydataframeamazon-webfirebasegoalgorithmazuredelphiVBAregexPowerSHellasp.net-coelasticseaspring-mvcruby-on-ramultithrealoopsgoogle-clovisual-stutomcatperfoRMANcapache-spaasp.net-mvTSQLgoogle-clounity3d.net-corevb.netfor-loopmavenmachine-leauthenticaherokuscaladjango-modpysparklinqobjective-if-statemesshasynchronodplyrwinformssecurityjpaentity-frauser-intergraphqlfirebase-roptimizatidebuggingdjango-vievariablesgoogle-bigmatlabparsingamazon-ec2xamarinnetworkingsql-serverrecursionintellij-iIntegercmakeentity-fraimage-procfirebase-asqlalchemydata-strucindexingspring-datautomationplsqlnlp

大佬总结

以上是大佬教程为你收集整理的ORACLE多行合并为一行全部内容,希望文章能够帮你解决ORACLE多行合并为一行所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。
标签: