- 浏览: 87369 次
- 性别:
- 来自: 厦门
最新评论
-
zhengyong7232:
SELECT t.province,DECODE(landsc ...
列转行 -
meepo3927:
不是特么很懂
列转行
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;
代码
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"
发表评论
-
oracle嵌套循环的执行计划优化
2013-06-15 23:28 0嵌套循环连接处理的 ... -
Oracle体系结构详解
2013-06-11 23:03 0Oracle体系结构就是围 ... -
ORACLE的隔离级别
2013-03-19 17:39 855隔离级别(isolation level) l ... -
Oracle 悲观锁和乐观锁
2013-03-19 17:30 1010一.丢失更新 所有多用户环境中存在丢失更新,丢失更新 ... -
Oracle异常
2012-10-16 21:48 4184Oracle异常处理异常处理是考验一个应用 程序 健壮性的最好 ... -
列转行
2012-07-12 11:51 1113oracle一个表或者视图中 ... -
oracle--多行转为一行的连接手段
2012-04-13 21:58 1089今天遇到将多行转为一行的一个操作,多谢oracle开发板的 w ... -
group by 多行变成一行-- (sum (case end case 也可以完成同样功能))
2012-04-13 21:52 3191select * from (select mtab.*, ... -
ORACLE实现多行合并一行的SQL写法
2012-04-12 16:55 1101项目中遇到一个需求, ... -
Oracle合并多行为多列[转]
2012-04-12 16:39 1368oracle 如何聚合多行函数 在BEA论坛上看一位&quo ...
相关推荐
oracle10g,9i多行合并一行函数
SQL语句用with将列分割成多列存为临时表,再将多行某个字段拼接合并为一行
oracle纯SQL语句多行合并一行,中间用“,”隔开,方便快速的显示在页面上
Oracle多行记录合并/连接/聚合字符串的几种方法
Oracle 分割字符串 返回多行数据
oracle某个字段多行记录转化为一行,oracle某个字段多行记录转化为一行。
本文实例讲述了oracle实现多行合并的方法。分享给大家供大家参考。具体分析如下: 在写sql时,经常会有将某列的字段合并起来,比如将某人名下每个月的工资列示,但是每个人只能占一行。 像这种场景,可能用行列转换...
多行记录合并 采用的是方法4,希望对你们有用
介绍了将多行转为字符串的三种方案,并比较了三种方案的执行效率. 1.sys_connect_by_path + start with ... connect by ... prior + 分析函数 2.自定义Function/SP 3.使用 Oracle 10g 内置函数 wmsys.wm_concat
oracle数据库一行拆成多行.sql
关于ORACLE分析函数的教程,教程描述描述清晰
ORACLE 分析函数大全,包含很多关于ORACLE的分析函数,内置函数
java调用oracle输出参数为多行多列的结构化数组的存储过程,并将其遍历解析;该demo部署后,只需要配置poolman.xml数据源,即可运行;
比如一个字段'11,22,22,33,44',一个查询语句显示成4行,就可以这样来实现。 11 22 22 33 44
文档详细介绍了oracle的分析函数,包括功能说明、sql示例等。分析函数功能强大,在报表或数据迁移的时候可能会使用到。分析函数用法看上去有点复杂,最好使用的时候,参考文档
很好的资料 共享给大家 很好研读 增强理解 没事多看看 开发一定没问题
1.被集合字段范围小且固定型 灵活性 性能 难度 2.固定表固定字段函数法 灵活性 性能 难度 3.灵活表函数法 灵活性 性能 难度 4.一条SQL法 灵活性
Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是对于每个组返回多行,而聚合函数对于每个组只返回一行。
Oracle分析函数的示例数据 其实也就是Mastering oracle sql书的SQL脚本