一、级联数据的表示:
首先我们来看一张关于组织架构的结构图
这张图是一个典型的“树型结构图”,只有一个根节点(King),其下有若干个分支节点,每个分支节点下又有若干个子节点或树叶节点。假如我们要把这些关系信息映射到数据库中(此处以Oracle9i数据库为例),表结构应当如何表示呢?
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
请注意这里红色粗体部分,字段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在不同位置时带来的不同结果。
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
需求:我们要找出员工ID为2的人及其所有下属(包括直接和间接下属)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
请注意PRIOR操作符被放置在字段ID前面。查询结果中ID为7的员工ben,虽然其对应的经理ID为4,但是因为员工号为4的tom,其对应的经理ID为2,所以ben是属于mark的间接下属而符合查询条件。
我们已经知道PRIOR放在那一侧,那一侧的字段就会被当成父记录的字段而被用于和当前记录的字段(另一侧的表达式)进行比较,那么假如我们把PRIOR放在manager_id一侧,结果会有什么不同吗?请看下面的SQL执行结果。
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
很明显结果完全不同,那么是什么造成了两次查询的结果完全不同呢?说到这里我们还要再回到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)