Oracle树查询的最重要的就是
select...start with... connect by ...prior
语法了。依托于该语法,我们可以将一个表形结构的中以树的顺序列出来。在下面列述了Oracle中树型查询的常用查询方式以及经常使用的与树查询相关的Oracle特性函数等,在这里只涉及到一张表中的树查询方式而不涉及多表中的关联等。
列表结构如下:
CREATE TABLE TREEDATA(
ID NUMBER NOT NULL,
NAME VARCHAR2(20),
REMARK VARCHAR2(1024),
PID NUMBER
)
PID存储的是父ID,如果是顶级父节点,该PID为0(表中最好别有null记录,这会引起全文扫描,建议改成0代替)。
我们从最基本的操作,逐步列出树查询中常见的操作,所以查询出来的节点以家族中的辈份作比方。
1. 查找树中的所有顶级父节点(辈份最长的人)。
假设这个树是个目录结构,那么第一个操作总是找出所有的顶级节点,再根据该节点找到其下属节点。
SELECT * FROM TREEDATA WHERE PID = 0;
这是个引子,没用到树型查询。
2.查找一个节点的直属子节点(所有儿子)。
如果查找的是直属子类节点,也是不用用到树型查询的。
SELECT * FROM TREEDATA WHERE PID = 1;
这个可以找到ID为1的直属子类节点。
3.查找一个节点的所有 直属子节点(所有后代)。
SELECT * FROM TREEDATA START WITH ID = 1 CONNECT BY PID = PRIOR ID;
这个查找的是ID为1的节点下的所有直属子类节点,包括子辈的和孙子辈的所有直属节点。
4.查找一个节点的直属父节点(父亲)。
如果查找的是节点的直属父节点,也是不用用到树型查询的。
SELECT b.* FROM TREEDATA a JOIN TREEDATA b ON a.PID = b.ID WHERE a.ID = 1;
这个找到的是ID为1的节点的直属父节点,要用到同一张表的关联了。
5.查找一个节点的所有直属父节点(祖宗)。
SELECT * FROM TREEDATA START WITH ID = 1 CONNECT BY PRIOR PID = ID;
这里查找的就是ID为1的所有直属父节点,打个比方就是找到一个人的父亲、祖父等。但是值得注意的是这个查询出来的结果的顺序是先列出子类节点再列出父类节点,姑且认为是个倒序吧。
上面列出两个树型查询方式,第3条语句和第5条语句,这两条语句之间的区别在于prior关键字的位置不同,所以决定了查询的方式不同。 当PID= PRIOR ID时,数据库会根据当前的ID迭代出PID与该ID相同的记录,所以查询的结果是迭代出了所有的子类记录;而PRIOR ID = PID时,数据库会跟据当前的PID来迭代出与当前的PID相同的ID的记录,所以查询出来的结果就是所有的父类结果。
以下是一系列针对树结构的更深层次的查询,这里的查询不一定是最优的查询方式,或许只是其中的一种实现而已。
6.查询一个节点的兄弟节点(亲兄弟)。
SELECT a.*
FROM TREEDATA a
WHERE EXISTS (SELECT *
FROM TREEDATA b
WHERE a.PID= b.PID AND b.ID = 1);
这里查询的就是与ID为1的节点同属一个父节点的节点了,就好比亲兄弟了。
7.查询与一个节点同级的节点(族兄弟)。
WITH tmp AS
(SELECT a.*, LEVEL lev
FROM TREEDATA a
START WITH a.PID = 0
CONNECT BY a.PID = PRIOR a.ID)
SELECT *
FROM tmp
WHERE lev = (SELECT lev
FROM tmp
WHERE ID = 1)
这里使用两个技巧,一个是使用了LEVEL来标识每个节点在表中的级别,还有就是使用with语法模拟出了一张带有级别的临时表。
8.查询一个节点的父节点的的兄弟节点(伯父与叔父)。
WITH tmp AS
(SELECT TREEDATA.*, LEVEL lev
FROM TREEDATA
START WITH PID = 0
CONNECT BY PID = PRIOR ID)
SELECT b.*
FROM tmp b,
(SELECT *
FROM tmp
WHERE ID = 1 AND lev = 2) a
WHERE b.lev = 1
UNION ALL
SELECT *
FROM tmp
WHERE PID = (SELECT DISTINCT x.ID
FROM tmp x,
tmp y,
(SELECT *
FROM tmp
WHERE ID = 1 AND lev > 2) z
WHERE y.ID = z.PID AND x.ID = y.PID);
这里查询分成以下几步。首先,将第7个一样,将全表都使用临时表加上级别;其次,根据级别来判断有几种类型,以上文中举的例子来说,有三种情况:(1)当前节点为顶级节点,即查询出来的lev值为1,那么它没有上级节点,不予考虑。(2)当前节点为2级节点,查询出来的lev值为2,那么就只要保证lev级别为1的就是其上级节点的兄弟节点。(3)其它情况就是3以及以上级别,那么就要选查询出来其上级的上级节点(祖父),再来判断祖父的下级节点都是属于该节点的上级节点的兄弟节点。
最后,就是使用UNION将查询出来的结果进行结合起来,形成结果集。
9.查询一个节点的父节点的同级节点(族叔)。
这个其实跟第7种情况是相同的。
WITH tmp AS
(SELECT a.*, LEVEL lev
FROM TREEDATA a
START WITH a.PID = 0
CONNECT BY a.PID= PRIOR a.ID)
SELECT *
FROM tmp
WHERE lev = (SELECT lev
FROM tmp
WHERE ID = 1) - 1
只需要做个级别判断就成了。
基本上,常见的查询在里面了,不常见的也有部分了。其中,查询的内容都是节点的基本信息,都是数据表中的基本字段,但是在树查询中还有些特殊需求,是对查询数据进行了处理的,常见的包括列出树路径等。
补充一个概念,对于数据库来说,根节点并不一定是在数据库中设计的顶级节点,对于数据库来说,根节点就是start with开始的地方。
下面列出的是一些与树相关的特殊需求。
10.名称要列出名称全部路径。
这里常见的有两种情况,一种是是从顶级列出,直到当前节点的名称(或者其它属性);一种是从当前节点列出,直到顶级节点的名称(或其它属性)。举地址为例:国内的习惯是从省开始、到市、到县、到居委会的,而国外的习惯正好相反。
从顶部开始:
SELECT SYS_CONNECT_BY_PATH (mc, '/')
FROM TREEDATA
WHERE ID = 1
START WITH PID = 0
CONNECT BY PID = PRIOR ID;
从当前节点开始:
SELECT SYS_CONNECT_BY_PATH (mc, '/')
FROM TREEDATA
START WITH ID = 1
CONNECT BY PRIOR PID= ID;
在这里我又不得不放个牢骚了。oracle只提供了一个sys_connect_by_path函数,却忘了字符串的连接的顺序。在上面的例子中,第一个SQL是从根节点开始遍历,而第二个SQL是直接找到当前节点,从效率上来说已经是千差万别,更关键的是第一个SQL只能选择一个节点,而第二个SQL却是遍历出了一颗树来。再次PS一下。
sys_connect_by_path函数就是从start with开始的地方开始遍历,并记下其遍历到的节点,start with开始的地方被视为根节点,将遍历到的路径根据函数中的分隔符,组成一个新的字符串,这个功能还是很强大的。
11.列出当前节点的根节点。
在前面说过,根节点就是start with开始的地方。
SELECT CONNECT_BY_ROOT mc, TREEDATA.*
FROM TREEDATA
START WITH ID = 1
CONNECT BY PRIOR PID = ID;
connect_by_root函数用来列的前面,记录的是当前节点的根节点的内容。
12.列出当前节点是否为叶子。
这个比较常见,尤其在动态目录中,在查出的内容是否还有下级节点时,这个函数是很适用的。
SELECT CONNECT_BY_ISLEAF, TREEDATA.*
FROM TREEDATA
START WITH PID = 0
CONNECT BY PID = PRIOR ID;
connect_by_isleaf函数用来判断当前节点是否包含下级节点,如果包含的话,说明不是叶子节点,这里返回0;反之,如果不包含下级节点,这里返回1。
分享到:
相关推荐
oracle语句非常强大,支持各种函数的查询,解决实际复杂的业务逻辑问题,比如:对一个表的父子查询,迭代查询等,这篇文档教你很快掌握oracle的树形结构查询,欢迎下载!
第5~12章是提高部分,讲解了正则表达式、分析函数、树形查询及汇总函数的用法。这部分知识常用于对一些复杂需求的实现及优化改写。最后两章介绍日常的优化改写案例。这部分是前面所学知识的扩展应用。, 如果您是开发...
第5~12章是提高部分,讲解了正则表达式、分析函数、树形查询及汇总函数的用法。这部分知识常用于对一些复杂需求的实现及优化改写。最后两章介绍日常的优化改写案例。这部分是前面所学知识的扩展应用。, 如果您是开发...
第5~12章是提高部分,讲解了正则表达式、分析函数、树形查询及汇总函数的用法。这部分知识常用于对一些复杂需求的实现及优化改写。最后两章介绍日常的优化改写案例。这部分是前面所学知识的扩展应用。, 如果您是开发...
第5~12章是提高部分,讲解了正则表达式、分析函数、树形查询及汇总函数的用法。这部分知识常用于对一些复杂需求的实现及优化改写。最后两章介绍日常的优化改写案例。这部分是前面所学知识的扩展应用。 如果您是开发...
内含很多oracle的语法和常用函数,包括 对于oracle进行简单树查询(递归查询),oracle内置函数大全, PLSQL 编程手册(SQL大全),及分页建表等很多学习资料。
oracle函数大全.chm
模式和便于存储、检索、更新、查询的函数集。它由以下组件构 成:一种描述几何数据存储、语法、语义的模式MDSYS;一种空间 索引机制SDO—INDEX;一组实现感兴趣区域查询和空间联合查 询的算子和函数;一组管理工具 ...
expimp导出导入工具的使用.doc,Oracle 单记录函数.doc,Oracle 的内存管理.ppt,Oracle9I计划管理表空间.pdf,Oracle备份与恢复.doc,Oracle返回树关系列表Sql.doc,Oracle里时间的应用.doc,Oracle体系结构概述....
主要给大家介绍了关于Oracle如何通过递归查询父子兄弟节点的相关资料,递归查询对各位程序员来说应该都不陌生,文中通过示例代码介绍的非常详细,需要的朋友可以参考借鉴,下面随着小编来一起学习学习吧。
开发中大家应该都做过什么类似部门管理这样的功能,一般情况下一个部门下面还有下一级部门(子部门),这个层级就类似一棵树。这种情况下一般会把父级部门和子级部门分成2个或者多个表,这种算是比较常规的做法;...
oracle数据库中XML字段的操作,--通过查询节点id返回以父节点为根节点的整棵节点树 SELECT extract(value(t),'/chapter').getstringval() ChapterName FROM t_content_metadata,TABLE( xmlsequence ( extract(XML_...
Oracle是当今各领域中应用比较广泛的一个大型关系型数据库产品,它以其所拥有的稳定、可伸缩性强以及安全可靠等...读者对象:面向各种培训班学员,高校相关专业的学生, Oracle应用开发人员以及Oracle数据库的学习者。
《Oracle Database 11g完全参考手册》全面详细地介绍了Oracle Database 11g的强大功能,阐述了如何使用所有的新增功能和工具,如何执行功能强大的SOL查询,如何编写PL/SQL和SQL*Plus语句,如何使用大对象和对象,...