文档章节

ORACLE多行合并为一行

ThinkGem
 ThinkGem
发布于 2016/07/17 16:15
字数 1116
阅读 23
收藏 0

 

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

  select ename,deptno from emp order by deptno;

   

ENAME DEPTNO
CLARK 10
KING 10
MILLER 10
SMITH 20
ADAMS 20
FORD 20
SCOTT 20
JONES 20
ALLEN 30
BLAKE 30
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;

DEPTNO ENAME RANK
10 CLARK 1
10 KING 2
10 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 MARTIN 4
30 TURNER 5
30 WARD 6


  可看出,经过row_number()后,部门人已经按部门和人名进行了排序,并打上了一个位置字段rank

2、利用oracle的递归查询connect by进行表内递归,并通过sys_connect_by_path进行父子数据追溯串的构造,这里要针对ename字段进行构造,使之合并在一个字段内(数据很多,只截取部分)

  select deptno,ename,rank,level as curr_level,

  ltrim(sys_connect_by_path(ename,','),',') ename_path from (

  select deptno,ename,row_number() over(partition by deptno order by deptno,ename) rank

  from emp order by 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 MILLER 3 3 CLARK,KING,MILLER
10 KING 2 1 KING
10 MILLER 3 2 KING,MILLER
10 MILLER 3 1 MILLER

 

DEPTNO ENAME RANK CURR_LEVEL ENAME_PATH
20 ADAMS 1 1 ADAMS
20 FORD 2 2 ADAMS,FORD
20 JONES 3 3 ADAMS,FORD,JONES
20 SCOTT 4 4 ADAMS,FORD,JONES,SCOTT
20 SMITH 5 5 ADAMS,FORD,JONES,SCOTT,SMITH
20 FORD 2 1 FORD
20 JONES 3 2 FORD,JONES
20 SCOTT 4 3 FORD,JONES,SCOTT
20 SMITH 5 4 FORD,JONES,SCOTT,SMITH
20 JONES 3 1 JONES
20 SCOTT 4 2 JONES,SCOTT
20 SMITH 5 3 JONES,SCOTT,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,row_number() over(partition by deptno order by deptno,curr_level desc) ename_path_rank from (select deptno,ename,rank,level as curr_level,

  ltrim(sys_connect_by_path(ename,','),',') ename_path from (

  select deptno,ename,row_number() over(partition by deptno order by deptno,ename) rank

  from emp order by deptno,ename) connect by deptno = prior deptno and rank-1 = prior rank);

DEPTNO ENAME_PATH ENAME_PATH_RANK
10 CLARK,KING,MILLER 1
10 CLARK,KING 2
10 KING,MILLER 3
10 CLARK 4
10 KING 5
10 MILLER 6
DEPTNO ENAME_PATH ENAME_PATH_RANK
20 ADAMS,FORD,JONES,SCOTT,SMITH 1
20 ADAMS,FORD,JONES,SCOTT 2
20 FORD,JONES,SCOTT,SMITH 3
20 ADAMS,FORD,JONES 4
20 FORD,JONES,SCOTT 5
20 JONES,SCOTT,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,ename_path,

  row_number() over(partition by deptno order by deptno,curr_level desc) ename_path_rank

  from (select deptno,ename,rank,level as curr_level,

  ltrim(sys_connect_by_path(ename,','),',') ename_path from (

  select deptno,ename,row_number() over(partition by deptno order by deptno,ename) rank

  from emp order by deptno,ename) connect by deptno = prior deptno and rank-1 = prior rank))

  where ename_path_rank=1;

 

ORACLE多行合并为一行。代码
select deptno, ename_path
  
from (select deptno,
               ename_path,
               row_number() 
over(partition by deptno order by deptno, curr_level desc) ename_path_rank
          
from (
          

          
          
select       empno,     
                       deptno,
                       ename,
                       rank,
                       
level as curr_level,
                       
ltrim(sys_connect_by_path(ename, ','), ',') ename_path
                  
from (select deptno,
                               ename,
                               empno,
                               row_number() 
over(partition by deptno order by deptno, ename) rank
                          
from emp
                         
order by deptno, ename)
                connect 
by deptno = prior deptno
                       
and rank - 1 = prior rank
               
 
                ))  
where ename_path_rank = 1;

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

例:   id  name
       1   aa
       2   bb
       3   cc

  要的结果是"aa,bb,cc"

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"

本文转载自:http://thinkgem.iteye.com/blog/1600438

共有 人打赏支持
上一篇: Java冒泡排序算法
下一篇: OGNL
ThinkGem

ThinkGem

粉丝 1069
博文 138
码字总数 24109
作品 2
济南
架构师
私信 提问
Oracle行转列

如果是oracle 10g以上 一个wm_concat函数就可以解决。 如果是oracle 9i以下就有点麻烦了。 表结构如下: NAME Null Type NSECCODE NOT NULL CHAR(6) CRESEARCHERCODE NOT NULL VARCHAR2(20)...

竹逸风
2016/06/22
9
0
Oracle 行转列pivot 、列转行unpivot 的Sql语句总结

转自:http://blog.csdn.net/xiaokui_wingfly/article/details/42419207 多行转字符串 这个比较简单,用||或concat函数可以实现 实际上就是拆分字符串的问题,可以使用 substr、instr、regex...

Amui
2016/06/29
373
0
pandas的Dataframe选取插入删除操作

数据 from pandas import DataFrame, Seriesimport pandas as pdimport numpy as np contries = { } d1 = DataFrame(contries)d1 2013 10 11 18 122014 20 22 16 252015 30 38 27 33 获取元素......

My_
2017/12/22
0
0
[转]Oracle表连接方式学习

出自:http://www.itpub.net/thread-207865-1-1.html 一般常见的Oracle数据库连接有3种: Nested Loop,Merge,Hash Join 1.嵌套循环联接NL(Neeted Loop) 嵌套循环联接也称为嵌套迭代,它将一...

吐槽的达达仔
2014/12/05
0
0
Sublime Text 3 快捷键总结

选择类 Ctrl+D 选中光标所占的文本,继续操作则会选中下一个相同的文本。 Alt+F3 选中文本按下快捷键,即可一次性选择全部的相同文本进行同时编辑。举个栗子:快速选中并更改所有相同的变量名...

川娃子丶
2016/12/08
4
0

没有更多内容

加载失败,请刷新页面

加载更多

追加字节能优化性能

这种方式看起来很神奇,单如果深入理解处理器架构就能理解其中的奥秘。让我们先来看看LinkedTransferQueue这个类,它使用一个内部类型来定义队列的头队列Head和尾节点tail,二这个内部类Pad...

无知的小狼
22分钟前
0
0
性能优化(JVM概念及配置)

虚拟机组成 一次编写,到处运行 Java虚拟机是对操作系统的模拟,隔离差异 2018编程语言排行榜 一个复杂的构架 虚拟机的内部概念 运行原理 编译器,解释器执行流程 内存分配-线程模型 栈帧模型...

这很耳东先生
29分钟前
3
0
Scala之初步认识与环境准备

1. 了解 Scala 1.1. 什么是 Scala Scala 是 Scalable Language 的简写,是一门多范式的编程语言。 Scala设计的初衷是要集成面向对象编程和函数式编程的各种特性。Scala运行于Java平台(Java虚...

飞鱼说编程
50分钟前
4
0
Vue项目分环境打包的实现步骤

在项目开发中,我们的项目一般分为开发版、测试版、Pre版、Prod版。Vue-cli的默认环境一只有dev和prod两个,之前每次要发布测试版或Pre版都是修改了源码中API地址后打包,这样很麻烦。如果能...

peakedness丶
50分钟前
6
0
vue+lowdb+express

搭建流程: 1.安装Node.js; 2.安装npm; 3.安装Express; 搭建流程 npm install -g express 安装 express 生成器 npm install -g express-generator 查看是否安装成功,随便输的一个命令...

Js_Mei
55分钟前
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部