Oracle递归查询(start with)向上查询和向下查询

令人迷糊的Oracle递归查询(start with)

读了上面文章，对递归查询有了初步理解，

创建Tree表如下：

基本含义：

select * from TREE start with FATHER = '爷爷' connect by FATHER = prior SON;

此查询意思就是，从 FATHER = '爷爷' 开始查询，将前面查询到的 SON 作为后面递归查询的条件。

如果改为 start with FATHER = '爸爸'，只是起点不同，会少一条记录。

上面的查询也可改为如下，查询结果相同。

select * from TREE start with SON = '爸爸' connect by FATHER = prior SON;

 

对原文第四点进行补充：

connect by 后面 son 和 father 顺序可以互换，

即“CONNECT BY PRIOR son = father;” 也可以写成 ”CONNECT BY father = PRIOR son;”

查询结果是一样的：

个人更倾向于前面的写法，更符合SQL条件查询格式，即 某列名 = 某值，

向下查询和向上查询：

以SON=“爸爸”为起始点，

向上查询：

select * from tree start with son = '爸爸' connect by prior father = son;

select * from tree start with son = '爸爸' connect by son = prior father;

向下查询：

select * from tree start with son = '爸爸' connect by prior son = father; 

select * from tree start with son = '爸爸' connect by father = prior son;