没看懂 connect by 是怎么循环的

Question

看到别人写的 connect by 的一个例子，但是没有看明白是怎么循环的，还请各位大虾指点一下，例子如下：
with   x  as
    ( select 'aa' chr from dual
         union all
      select 'bb' chr from dual)
select level ,chr,lpad( ' ' ,( level - 1 )* 5 , '-' )||chr other from x connect by level <= 2结果：
level               chr                 other
1                      aa                    aa
2                      aa                  ---- aa
2                      bb                  ---- bb
1                      bb                   bb
2                      aa                 ---- aa
2                      bb                 ---- bb

Answer 1

Oracle中connect by...start with...的使用一、语法 
大致写法：select * from some_table [where 条件1] connect by [条件2] start with [条件3]; 
其中 connect by 与 start with 语句摆放的先后顺序不影响查询的结果，[where 条件1]可以不需要。 
[where 条件1]、[条件2]、[条件3]各自作用的范围都不相同： [where 条件1]是在根据“connect by [条件2] start with [条件3]”选择出来的记录中进行过滤，是针对单条记录的过滤， 不会考虑树的结构； [条件2]指定构造树的条件，以及对树分支的过滤条件，在这里执行的过滤会把符合条件的记录及其下的所有子节点都过滤掉； [条件3]限定作为搜索起始点的条件，如果是自上而下的搜索则是限定作为根节点的条件，如果是自下而上的搜索则是限定作为叶子节点的条件； 示例： 
假如有如下结构的表：some_table(id,p_id,name)，其中p_id保存父记录的id。 
select * from some_table t where t.id!=123 connect by prior t.p_id=t.id and t.p_id!=321 start with t.p_id=33 or t.p_id=66; 对prior的说明： 
    prior存在于[条件2]中，可以不要，不要的时候只能查找到符合“start with [条件3]”的记录，不会在寻找这些记录的子节点。要的时候有两种写法：connect by prior t.p_id=t.id 或 connect by t.p_id=prior t.id，前一种写法表示采用自上而下的搜索方式（先找父节点然后找子节点），后一种写法表示采用自下而上的搜索方式（先找叶子节点然后找父节点）。 二、执行原理 
connect by...start with...的执行原理可以用以下一段程序的执行以及对存储过程RECURSE()的调用来说明： /* 遍历表中的每条记录，对比是否满足start with后的条件，如果不满足则继续下一条， 
如果满足则以该记录为根节点，然后调用RECURSE()递归寻找该节点下的子节点， 
如此循环直到遍历完整个表的所有记录 。*/ 
for rec in (select * from some_table) loop 
if FULLFILLS_START_WITH_CONDITION(rec) then 
    RECURSE(rec, rec.child); 
end if; 
end loop; /* 寻找子节点的存储过程*/ 
procedure RECURSE (rec in MATCHES_SELECT_STMT, new_parent IN field_type) is 
begin 
APPEND_RESULT_LIST(rec); /*把记录加入结果集合中*/ 
/*再次遍历表中的所有记录，对比是否满足connect by后的条件，如果不满足则继续下一条， 
如果满足则再以该记录为根节点，然后调用RECURSE()继续递归寻找该节点下的子节点， 
如此循环直到找至叶子节点。*/ 
for rec_recurse in (select * from some_table) loop 
    if FULLFILLS_CONNECT_BY_CONDITION(rec_recurse.child, new_parent) then 
      RECURSE(rec_recurse,rec_recurse.child); 
    end if; 
end loop; 
end procedure RECURSE; 三、使用探讨 
    从上面的执行原理可以看到“connect by...start with...”构造树的方式是：（1）如果是自上而下方式，则把表中的每一条记录都作为根节点来生成树，所以表中有多少条记录就会构造出多少棵树。（2）如果是自下而上的搜索方式，则把表中的每一条记录都作为叶子节点来生成分支，所以表中有多少条记录就会生成多少条分支。 
    因此如果表中的记录不是严格遵照每条记录都只能有一个父记录的原则，那么就可能有部分记录会存在于多棵树中，那么在查找记录的时候就可能会出现找到多条重复记录的异常情况。

  

Answer 2

--1.Hierarchical Queries: START WITH and CONNECT BY PRIOR clauses 
--Hierarchical Queries
--START WITH and CONNECT BY PRIOR clauses.SELECT employee_id, manager_id, first_name, last_name
FROM employee_jh
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id;EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME
----------- ---------- ---------- ----------
          1          0 James      Smith
          2          1 Ron        Johnson
          3          2 Fred       Hobbs
          5          2 Rob        Green
          4          1 Susan      Jones
          6          4 Jane       Brown
          9          6 Henry      Heyson
          7          4 John       Grey
          8          7 Jean       Blue
         10          1 Kevin      Black
         11         10 Keith      Long
         12         10 Frank      Howard
         13         10 Doreen     Penn13 rows selected.--2.Using a Subquery in a START WITH Clause
SELECT LEVEL,
       LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' || last_name AS employee
FROM employee_jh
START WITH employee_id = (SELECT employee_id FROM employee_jh WHERE first_name = 'Kevin' AND last_name = 'Black')
CONNECT BY PRIOR employee_id = manager_id;     LEVEL EMPLOYEE
---------- -------------------------
         1  Kevin Black
         2    Keith Long
         2    Frank Howard
         2    Doreen Penn--3.Including Other Conditions in a Hierarchical Query
SELECT LEVEL,
       LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' ||
       last_name AS employee, salary
FROM employee_jh
WHERE salary <= 50000
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id;     LEVEL EMPLOYEE                      SALARY
---------- ------------------------- ----------
         3      Rob Green                 40000
         3      Jane Brown                45000
         4        Henry Heyson            30000
         3      John Grey                 30000
         4        Jean Blue               29000
         3      Keith Long                50000
         3      Frank Howard              45000
         3      Doreen Penn               470008 rows selected.

Answer 3

我是想问connect by 不带 start with 这种循环是怎样循环的？

Answer 4

start是指定根的,如果没有start,oracle将以表中所有数据做为根.

Answer 5

类似select中没有where时,选出表中所有数据

Answer 6

使用connect by时,首先考虑的就是start with,即先确定以那些数据为根进行递归,然后考虑connect by,即如何通过根去关联树枝及叶子,然后考虑where,如何对上面的结果再次进行筛选,得到自己需要的记录.

Answer 7

select level ,chr,lpad( ' ' ,( level - 1 )* 5 , '-' )||chr other from x connect by level <= 2，是不是循环2遍，但是结果怎么就会出现了6条记录了？ 能以我这个例子来说明一下吗？谢谢大虾们！！！

Answer 8

由于没有start with,说明将以表中所有数据为根.
首先取'aa'为根,根据connect by找到aa及bb为其叶子,此为3条记录
然后取'bb'为根,根据connect by找到aa及bb为其叶子,此为3条记录
所以总共是6条.

Answer 9

递归是这样取的.首先根据start with 确定根,然后对根挨条递归.
首先以根为level=1的数据
然后对根中数据一次去按connect by查找下一层数据即level为2的数据
..........
然后对根中数据一次去按connect by查找下一层数据即level为n的数据
直到没有符合条件的记录为止.
由于限制条件是level<=2,所以第一次查找时所有的数据都符合条件.
再次查找时因为level=3所以终止.

Answer 10

level1  aa bb
level2  aa     bb
       aa bb  aa bb
level3    aa              bb
        aa    bb      aa     bb
      aa  bb aa bb   aa bb  aa bb
大概就是这样 楼主可以把level设大一点，或者把字符数设多一点 aa bb cc
自己推理一下 差不多就能明白了

Answer 11

感谢minitoy这位大虾，感谢您的讲解！！

Answer 12

是啊 minitoy真是oracle大佬啊 不服不行