START WITH and CONNECT BY in Oracle SQL

In the following example, the table from which that data is selected consists of just these attributes: parent and child. We make sure (by means of a unique constraint) that the child is uniqe within the table. This is just like in the real life where (as of yet) a child cannot have two different mothers.

The data filled into the table is such that a the sum over the children with the same parent is the value of the parent:

set feedback off

create table test_connect_by (
  parent     number,
  child      number,
  constraint uq_tcb unique (child)
);

5 = 2+3

insert into test_connect_by values ( 5, 2);
insert into test_connect_by values ( 5, 3);

18 = 11+7

insert into test_connect_by values (18,11);
insert into test_connect_by values (18, 7);

17 = 9+8

insert into test_connect_by values (17, 9);
insert into test_connect_by values (17, 8);

26 = 13+1+12

insert into test_connect_by values (26,13);
insert into test_connect_by values (26, 1);
insert into test_connect_by values (26,12);

15=10+5

insert into test_connect_by values (15,10);
insert into test_connect_by values (15, 5);

38=15+17+6

insert into test_connect_by values (38,15);
insert into test_connect_by values (38,17);
insert into test_connect_by values (38, 6);

38, 26 and 18 have no parents (the parent is  null)

insert into test_connect_by values (null, 38);
insert into test_connect_by values (null, 26);
insert into test_connect_by values (null, 18);

Now, let's select the data hierarchically:

select lpad(' ',2*(level-1)) || to_char(child) s 
  from test_connect_by 
  start with parent is null
  connect by prior child = parent;

This select statement results in:

38
  15
    10
    5
      2
      3
  17
    9
    8
  6
26
  13
  1
  12
18
  11
  7

解释：

connect by 是结构化查询中用到的，其基本语法是： 
select ... from tablename start with 条件1 
connect by 条件2 
where 条件3;

条件1 是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。 也可以以叶节点为起始节点。
条件2 是连接条件，其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR org_id = parent_id就是说上一条记录的org_id 是本条记录的parent_id，即本记录的父亲是上一条记录。 
条件3 是过滤条件，用于对返回的所有记录进行过滤。

以上边的example为例，start with parent is null, 首先检索出parent为null,child为38的记录，connect by prior child = parent,表示上一条的child为当前的parent，所以检索出parent为38，child为15，再检索出以15为parent的记录parent为15，child为10，由于记录中没有以10为parent的记录，继续找以10的parent（即15）为parent的记录，找到parent为15，child为5的记录，记录中没有以5为parent的记录，继续着以5的parent（即15）为parent的记录，也没有以15为parent的记录了，就继续找以15的parent（即38）为parent的记录，找到parent为38，child为17的记录，接着找以17为parent的记录......以此类推。