注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

一路奔跑

奔跑着一路向前...

 
 
 

日志

 
 

级联查询(Hierarchical Queries)  

2012-06-05 09:45:21|  分类: Oracle书架 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

一、级联数据的表示:
首先我们来看一张关于组织架构的结构图

这张图是一个典型的“树型结构图”,只有一个根节点(King),其下有若干个分支节点,每个分支节点下又有若干个子节点或树叶节点。假如我们要把这些关系信息映射到数据库中(此处以Oracle9i数据库为例),表结构应当如何表示呢?


CREATE TABLE EMPLOYEE (

EMP_ID          NUMBER (4) CONSTRAINT EMP_PK PRIMARY KEY,

FNAME           VARCHAR2 (15)NOT NULL, 

LNAME           VARCHAR2 (15)NOT NULL, 

DEPT_ID         NUMBER (2)NOT NULL,

MANAGER_EMP_ID  NUMBER (4) CONSTRAINT EMP_FK REFERENCES EMPLOYEE(EMP_ID),

SALARY          NUMBER (7,2)NOT NULL,

HIRE_DATE       DATENOT NULL, 

JOB_ID          NUMBER (3));
请注意这里红色粗体部分,字段MANAGER_EMP_ID的值引用了字段EMP_ID的值,我们称这种引用为“自引用”。它规定了经理人员的ID必须是来自表中存在的员工ID。
二、Oracle 9i中的start with...connect by:
[[START WITH condition1]  CONNECT BY condition2]
START WITH condition1
指定级联数据的根记录(一条或多条),所有满足条件1的记录都将被当成是根纪录,假如我们不给定START WITH子句,所有的纪录都会被当成是根纪录,通常这不是我们想要的结果。condition1可以是一个子查询。

CONNECT BY condition2
指定级联数据中父纪录和子纪录之间的关系,这里的关系被表示成一个表达式,当前纪录的字段会和对应的父纪录的某个字段进行比较。condition2必须跟着一个PRIOR操作符,该操作符用于标明父纪录的字段。condtion2不能包含子查询
PRIOR是Oracle的一个内建操作符,仅用于级联查询。当我们在级联查询的CONNECT BY条件中使用了PRIOR操作符时,位于其后的表达式被当成是当前纪录的父纪录进行比较。
三、实例比较:
下面我们通过2条SQL语句来演示如何进行级联查询,以及PRIOR在不同位置时带来的不同结果。


SQL> select * from employee;

        ID EMP_NAME             MANAGER_ID
---------- -------------------- ----------
         1 king
         2 mark                          1
         3 bob                           1
         4 tom                           2
         5 paul                          2
         6 jack                          3
         7 ben                           4

7 rows selected.
需求:我们要找出员工ID为2的人及其所有下属(包括直接和间接下属)
SQL> select * from employee start with id = 2 connect by prior id = manager_id order by id;

        ID EMP_NAME             MANAGER_ID
---------- -------------------- ----------
         2 mark                          1
         4 tom                           2
         5 paul                          2
         7 ben                           4
请注意PRIOR操作符被放置在字段ID前面。查询结果中ID为7的员工ben,虽然其对应的经理ID为4,但是因为员工号为4的tom,其对应的经理ID为2,所以ben是属于mark的间接下属而符合查询条件。
我们已经知道PRIOR放在那一侧,那一侧的字段就会被当成父记录的字段而被用于和当前记录的字段(另一侧的表达式)进行比较,那么假如我们把PRIOR放在manager_id一侧,结果会有什么不同吗?请看下面的SQL执行结果。
SQL> select * from employee start with id = 2 connect by id = prior manager_id order by id;

        ID EMP_NAME             MANAGER_ID
---------- -------------------- ----------
         1 king
         2 mark                          1
很明显结果完全不同,那么是什么造成了两次查询的结果完全不同呢?说到这里我们还要再回到SQL语言本身,我用一种比较直白的方式来讲解不同位置的PRIROR所带来的不同意义。
【1】第一个查询:connect by prior id = manager_id,意思是从当前根记录开始,查找所有符合条件的记录:他们的manager_id必须等于当前记录的id。也就是说查找所有manager_id=2的记录及其子记录,很明显manager_id=2的记录只有tom和paul,但是由于ben的直接领导tom是mark的下属,所以ben也是mark的下属,只不过是间接关系而已。
【2】第二个查询:connect by id = prior manager_id,意思是从当前根记录开始,查找所有符合条件的记录:他们的id必须等于当前记录的manager_id。也就是说查找所以id=1的记录,那么很明显id=1的记录只有king。
总结:Prior放在那里,那一侧就是被比较的一方(父方),另一侧就是发起比较的一方(子方)。语义上可以这样翻译:xxx字段的值必须等于当前记录XXX字段的值(prior一方)
  评论这张
 
阅读(249)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018