- 浏览: 143245 次
- 性别:
- 来自: 南京
文章分类
- 全部博客 (129)
- framework (18)
- Java (25)
- web2.0 (7)
- Oracle (23)
- 框架师 (20)
- design pattern (5)
- j2ee (6)
- 日常问题收集 (51)
- linux (12)
- Oralce优化 (4)
- life (3)
- sqlplus (1)
- sql (2)
- C# (4)
- mysql (9)
- 拿来主义 (12)
- 标签 (0)
- ThinkPHP (1)
- python (1)
- mybatis (2)
- html5 (1)
- jquery (2)
- hadoop (3)
- git (1)
- struts2 (1)
- shiro (1)
- struts (1)
最新评论
-
xiao88xian:
...
jQuery常用经典技巧
来至:http://blog.oracle.com.cn/html/83/t-122083.html
简单的树型结构
关于树的普通应用
学习了下这个函数, 用ORGINDUSTRIES的表做了个测试:
正常的树型结构
select lpad(' ',6*(level-1))||industry,indlevel,indid,pindid
from ORGINDUSTRIES
start with indid=1
connect by pindid=prior indid
结果显示如下
Indlevel indid pindid
服装与服饰 1 1 0
服装 2 2 1
女装 3 3 2
倒型树
下面这个例子是个”倒数”—倒过来的树型结构
select lpad(' ',6*(level-1))||industry,indlevel,indid,pindid
from ORGINDUSTRIES
start with indid=20
connect by indid=prior pindid;
这是标准结果:
Indlevel indid pindid
二手服装 3 20 2
服装 2 2 1
服装与服饰 1 1 0
结论
无论正树还是倒树, 关键就在于connect by的条件.
正树: 必须是 ‘父’= prior ‘子’
倒树: 必须是 ‘子’= prior ‘父’
树型结构的条件过滤
采用树型结构的话, 如果我们想将树上的一个分支砍掉. 将分支后面的结构都抛弃掉, 这个可以实现麽?当然可以。 但是不是用where, where条件只能去除单一的条件。
所以, 这种树型的过滤条件就需要加在connect by上面。
测试如下:由于用真实环境比较贴近实际,所以提前用下SYS_CONNECT_BY_PATH函数来显示下环境
不加任何条件的环境:
select areaname,sys_connect_by_path(areaname,',')
from areas bb
start with areaname='中国大陆'
connect by parentareaid=prior areaid
结果:
1 中国大陆,中国大陆
2 北京 ,中国大陆,北京
3 北京 ,中国大陆,北京,北京
4 东城区 ,中国大陆,北京,东城区
5 西城区 ,中国大陆,北京,西城区
22 广东 ,中国大陆,广东
23 广州 ,中国大陆,广东,广州
24 汕尾 ,中国大陆,广东,汕尾
25 潮阳 ,中国大陆,广东,潮阳
46 上海 ,中国大陆,上海
47 上海 ,中国大陆,上海,上海
48 黄浦区 ,中国大陆,上海,黄浦区
49 闸北区 ,中国大陆,上海,闸北区
加了where过滤条件的SQL:
select areaname,sys_connect_by_path(areaname,',')
from areas bb
where bb.areaid>861000
start with areaname='中国大陆'
connect by parentareaid=prior areaid
结果为:
2 北京 ,中国大陆,北京
3 北京 ,中国大陆,北京,北京
4 东城区 ,中国大陆,北京,东城区
5 西城区 ,中国大陆,北京,西城区
22 广东 ,中国大陆,广东
23 广州 ,中国大陆,广东,广州
24 汕尾 ,中国大陆,广东,汕尾
25 潮阳 ,中国大陆,广东,潮阳
46 上海 ,中国大陆,上海
47 上海 ,中国大陆,上海,上海
48 黄浦区 ,中国大陆,上海,黄浦区
49 闸北区 ,中国大陆,上海,闸北区
结论:去掉了“1 中国大陆,中国大陆”数据
加了connect by的过滤条件:
select areaname,sys_connect_by_path(areaname,',')
from areas bb
where bb.areaid>861000
start with areaname='中国大陆'
connect by parentareaid=prior areaid and areaname<>'广东'
结果为:
2 北京 ,中国大陆,北京
3 北京 ,中国大陆,北京,北京
4 东城区 ,中国大陆,北京,东城区
5 西城区 ,中国大陆,北京,西城区
46 上海 ,中国大陆,上海
47 上海 ,中国大陆,上海,上海
48 黄浦区 ,中国大陆,上海,黄浦区
49 闸北区 ,中国大陆,上海,闸北区
结论:去掉了整个广东的分支, 在结果集中只有北京和上海
SYS_CONNECT_BY_PATH函数
采用SYS_CONNECT_BY_PATH函数为:
select industry,sys_connect_by_path(industry,'/')
from ORGINDUSTRIES
start with indid=3
connect by indid=prior pindid;
结果为:
女装 /女装
服装 /女装/服装
服装与服饰 /女装/服装/服装与服饰
这样的话, 就可以实现, 树结构的结果集的单行拼接:
我们只需要取最大的字段就OK了
测试如下:
select max(sys_connect_by_path(industry,'/'))
from ORGINDUSTRIES
start with indid=3
connect by indid=prior pindid;
结果为:
/女装/服装/服装与服饰
复杂的树型结构――多列变单列
树型结构也分单树和多树(我的称呼,实际上就是指单支和多支)
对于下面的这种情况, 我们必须要构造的树就属于单支树。
原始环境
环境如下:
select * from test;
结果为:
1 n1
1 n2
1 n3
1 n4
1 n5
3 t1
3 t2
3 t3
3 t4
3 t5
3 t6
2 m1
造树
脚本如下:
select no,q,
no+row_number() over( order by no) rn,
row_number() over(partition by no order by no) rn1
from test
结果如下:
No Q RN RN1
1 n1 2 1
1 n2 3 2
1 n3 4 3
1 n4 5 4
1 n5 6 5
2 m1 8 1
3 t1 10 1
3 t2 11 2
3 t3 12 3
3 t4 13 4
3 t5 14 5
3 t6 15 6
每列的目的是:
RN1列主要的目的是分组, 按照value值‘1’,我们可以start with使用它。
RN列主要用来做connect by使用。 实际上它就是我们要的树。
第一个支: 2,3,4,5,6
第二个支: 8
第三个支: 10,11,12,13,14,15
中间为什么要断掉:7,9 目的就是为了区别每个分支。 到后面看具体的SQL,就明白这里的说法了。
杀手锏
既然我们有了树, 就可以使用树型函数SYS_CONNECT_BY_PATH和connect by啦,来拼接我们所需要的多列值。
脚本如下:
select no,sys_connect_by_path(q,',')
from (
select no,q,
no+row_number() over( order by no) rn,
row_number() over(partition by no order by no) rn1
from test
)
start with rn1=1
connect by rn-1=prior rn
结果为:
1 ,n1
1 ,n1,n2
1 ,n1,n2,n3
1 ,n1,n2,n3,n4
1 ,n1,n2,n3,n4,n5
2 ,m1
3 ,t1
3 ,t1,t2
3 ,t1,t2,t3
3 ,t1,t2,t3,t4
3 ,t1,t2,t3,t4,t5
3 ,t1,t2,t3,t4,t5,t6
终极武器
最终我们要的值,是单列值, 其实想想, 也就是最长的一行咯。 那么就好办了。 我们直接GROUP BY ,然后取MAX值。
脚本如下:
select no,max(sys_connect_by_path(q,','))
from (
select no,q,
no+row_number() over( order by no) rn,
row_number() over(partition by no order by no) rn1
from test
)
start with rn1=1
connect by rn-1=prior rn
group by no
结果为:
1 ,n1,n2,n3,n4,n5
2 ,m1
3 ,t1,t2,t3,t4,t5,t6
如果觉得前面的‘,’不好看,可以使用ltrim去掉。 或者用substr也可以。
如下:
ltrim(max(sys_connect_by_path(q,',')),',')
或者
substr(max(sys_connect_by_path(q,',')),2)
发表评论
-
ORACLE学习系列.1.安装篇 RHEL5.5 64位下安装Oracle 11g 64位安装前置条件的两种方法
2014-08-15 14:04 1135http://www.oracle-base.com/ar ... -
Ubuntu 12.04(32位)安装Oracle 11g(32位)全过程以及几乎所有问题的解决办法
2014-08-06 16:26 829from :http://blog.csdn.net/idb ... -
解决maven仓库中找不到ojdbc驱动的问题
2013-02-21 16:51 886maven仓库中的ojdbc下载需要oracle的官方授权, ... -
MySQL与Oracle的数据迁移注意事项,另附转换工具链接
2013-02-21 15:59 1812将数据从MySQL迁移到Oracle的注意事项 1. 自动 ... -
linux下新建oracle数据库实例
2012-10-20 16:33 8821、在Linux服务器的图形界面下,打开一个终端,输入如 ... -
Instance and Databases
2012-10-20 13:42 764实例和数据库的关系,其实真正的世界也有相似的关系 ... -
Oracle Index 学习
2012-10-11 21:58 8351.index需要储存空间和I/O ... -
sqlplus 常用set指令
2012-10-10 20:30 1043set colsep' '; ... -
ORACLE SPOOL 总结
2012-10-10 20:30 744spool常用的设置set colsep' '; ... -
sql loader 导入数据是报错:Oracle修改时间报:ORA-01830: 日期格式图片在转换整个输入字符串之前结束的解决办法
2012-10-10 13:12 2093在学习SQL Loader导入数据的时候,试用SCOT ... -
Linux下监听程序TNS-12543错误解决方法
2012-10-09 21:42 1366转载:http://blog.csdn.net/libi ... -
选择ORACLE,坚持走下去
2012-09-24 08:38 886工作之后感觉 ... -
linux 启动oracle
2012-09-23 10:34 756转载:http://rorom.iteye.com/bl ... -
ORA-01012: not logged on 解决办法
2012-09-16 22:54 1609conn / as sysdba 报错ORA-01012: n ... -
GROUP分组函数之ROLLUP
2012-09-16 17:36 968/****************** *ROLL ... -
MERGE函数的妙用案例
2012-09-16 16:44 900本文来至《剑破冰山 ... -
Windows 7-64位安装PLSQL-Developer
2012-09-16 16:41 1086WIN7-64位安装PLSQL-Developer步骤以下 ... -
使用Hint时的注意事项
2012-08-03 16:10 969在使用Hint时需要注意的一点是,并非任何时刻Hint都 ... -
hint提示的相关介绍
2012-08-03 16:05 983介绍hint的使用之前先了解RBO和CBOOracle的优化器 ... -
Oracle 中对中文字段进行排序通常有三种方式
2012-07-27 18:24 885Oracle 中对中文字段进行排序通常有三种方式 1) ...
相关推荐
值得注意的是,尽管这个方法成功实现了将非树结构数据转化为路径字符串,但它并不是`sys_connect_by_path`函数的典型应用场景。通常,`sys_connect_by_path`用于具有明显父子关系的树形数据,而在这个例子中,作者...
`CONNECT BY`还可以结合`SYS_CONNECT_BY_PATH`函数,用于返回从起始节点到当前节点的路径。例如,我们可以添加一个路径列来显示每个员工的晋升路径: ```sql SELECT EMPLOYEE_NAME, MANAGER_NAME, SYS_...
若需展示每个节点的完整路径,可以使用`SYS_CONNECT_BY_PATH`函数。这个函数会将从根节点到当前节点的路径串联起来,通常用特定字符(如'/')分隔各节点。 ```sql SELECT LEVEL, SYS_CONNECT_BY_PATH(a.ename, '/')...
`sys_connect_by_path`函数与`CONNECT BY`配合使用,可以追踪节点在整个树中的路径。例如,它可以将每个节点的路径作为字符串返回,便于理解和分析树结构。 在实际应用中,例如组织结构的展示,我们可以创建一个...
理解并熟练掌握`CONNECT BY`、`PRIOR`、`sys_connect_by_path`等关键字和函数,可以帮助我们更高效地处理树形结构的数据。在实际应用中,还需要考虑性能优化,如合理设计表结构和使用索引,以确保查询的效率。
### Oracle的Connect By...此外,结合其他函数(如`LPAD`和`SYS_CONNECT_BY_PATH`)使用,可以让查询结果更加直观易懂。这对于开发人员来说是一项非常有用的技能,特别是在处理复杂的组织结构或者产品分类等场景时。
总结来说,通过巧妙地运用Oracle的`CONNECT BY`和`SYS_CONNECT_BY_PATH`函数,我们可以实现多行数据到一行的合并,这对于报告展示和数据分析非常实用。不过要注意的是,这种方法在大数据量下可能会有性能问题,因为...
除了基本的查询语法,Oracle还提供了一些辅助函数来处理树结构,如`SYS_CONNECT_BY_PATH`可以返回节点在整个树路径中的位置,`LEVEL`返回节点在树中的深度,`CONNECT_BY_ROOT`则用于获取树的根节点。 在进行树查询...
- **利用`SYS_CONNECT_BY_PATH`函数**:可以生成路径字符串,常用于展示完整路径信息,例如`SELECT SYS_CONNECT_BY_PATH(name, '/') AS path`。 ### 总结 通过结合`START WITH`和`CONNECT BY`子句,Oracle数据库...
另一个示例是使用`SYS_CONNECT_BY_PATH`函数生成层次结构的数据。这个函数通常用于构建树形或层级关系的数据集。例如: ```sql SELECT ID, NAME, LTRIM(MAX(SYS_CONNECT_BY_PATH(ROLE, ',')), ',') FROM ( SELECT...
通过`START WITH`和`CONNECT BY`,我们可以遍历整个树结构,并通过`sys_connect_by_path`函数将路径转化为字符串,满足各种复杂的业务需求。在实际应用中,需要根据具体的数据表结构和业务需求进行适当的调整。
使用 `SYS_CONNECT_BY_PATH` 函数可以生成从根节点到指定节点的完整路径,每个节点间用指定字符(这里是 `/`)分隔。 8. **列出当前节点的根节点** `CONNECT_BY_ROOT` 函数用于标识根节点,结合 `START WITH ...
`SYS_CONNECT_BY_PATH`函数通常用于构建树状结构,但它也可以用于列合并。不过,需要注意的是,它的连接符号不能直接使用逗号,因为Oracle会报错。如果需要使用逗号,可以通过`REPLACE`函数进行替换。示例代码如下...
- **SYS_CONNECT_BY_PATH()**:用于构建层次路径。 - **ORDER SIBLINGS BY**:按照指定顺序排列同级节点。 - **The NOCYCLE**:防止查询进入无限循环。 通过遵循以上SQL最佳实践,可以显著提高数据库应用的性能和可...
Oracle树形查询法,通过使用`sys_connect_by_path`函数结合`start with`和`connect by`子句,可以构建出树状的查询结果。与内部编码法相比,该方法在插入和修改方面更为灵活,仅需知道父节点的ID即可轻松进行操作。...