posts - 262,  comments - 221,  trackbacks - 0

一、级联数据的表示:

首先我们来看一张关于组织架构的结构图




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

CREATE TABLE EMPLOYEE (

EMP_ID          
NUMBER (4CONSTRAINT EMP_PK PRIMARY KEY,

FNAME           
VARCHAR2 (15)NOT NULL

LNAME           
VARCHAR2 (15)NOT NULL

DEPT_ID         
NUMBER (2)NOT NULL,

MANAGER_EMP_ID  
NUMBER (4CONSTRAINT 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一方)

参考资料:《Mastering Oracle SQL》(By Alan Beaulieu, Sanjay Mishra O'Reilly June 2004  0-596-00632-2) 


-------------------------------------------------------------
生活就像打牌,不是要抓一手好牌,而是要尽力打好一手烂牌。
posted on 2008-06-11 17:55 Paul Lin 阅读(4061) 评论(6)  编辑  收藏 所属分类: Oracle 开发


FeedBack:
# re: Oracle开发专题之:级联查询(Hierarchical Queries)
2008-06-15 14:43 | 蓝剑
明白了,谢谢!  回复  更多评论
  
# re: 【原】Oracle开发专题之:级联查询(Hierarchical Queries)
2008-08-05 21:52 | ppp
非常谢谢!很好!  回复  更多评论
  
# re: 【原】Oracle开发专题之:级联查询(Hierarchical Queries)
2008-08-29 18:30 | xwu
看起来很清晰,很好,谢谢。  回复  更多评论
  
# re: 【原】Oracle开发专题之:级联查询(Hierarchical Queries)
2009-03-16 10:27 | 取经
非常好  回复  更多评论
  
# re: 【原】Oracle开发专题之:级联查询(Hierarchical Queries)
2009-05-17 17:12 | sharepub
pengpenglin你好,看了写的原创技术文章,发现你的技术和文字功底还不错,不知道是否有时间来写ORACLE 11G开发相关的教程呢?我们是人民邮电出版社和电子工业出版社在成都的编辑中心,易为科技,最近我们刚好有相关教程的出版计划,如果有意向欢迎进一步联系:
msn: sharepub@hotmail.com
qq: 568001705
email: wondering2004@sina.com   回复  更多评论
  
# re: 【原】Oracle开发专题之:级联查询(Hierarchical Queries)
2011-11-04 15:21 | dgds
楼主好人啊  回复  更多评论
  

只有注册用户登录后才能发表评论。


网站导航:
 
<2008年6月>
25262728293031
1234567
891011121314
15161718192021
22232425262728
293012345

常用链接

留言簿(19)

随笔分类

随笔档案

BlogJava热点博客

好友博客

搜索

  •  

最新评论

阅读排行榜

评论排行榜