今天事情不是很多，就小结了一下树形查询start with .. . connect by ...的各种用法。 下面以个例子展开。 SQL>create table test_connect_by (child number,parent number); insert into TEST_CONNECT_BY (CHILD, PARENT) values (2, 5); insert into TEST_CONNECT_BY (CHILD, PARENT) values (3, 5); insert into TEST_CONNECT_BY (CHILD, PARENT) values (10, 15); insert into TEST_CONNECT_BY (CHILD, PARENT) values (5, 15); insert into TEST_CONNECT_BY (CHILD, PARENT) values (9, 17); insert into TEST_CONNECT_BY (CHILD, PARENT) values (8, 17); insert into TEST_CONNECT_BY (CHILD, PARENT) values (15, 38); insert into TEST_CONNECT_BY (CHILD, PARENT) values (17, 38); insert into TEST_CONNECT_BY (CHILD, PARENT) values (6, 38); insert into TEST_CONNECT_BY (CHILD, PARENT) values (13, 26); insert into TEST_CONNECT_BY (CHILD, PARENT) values (1, 26); insert into TEST_CONNECT_BY (CHILD, PARENT) values (12, 26); insert into TEST_CONNECT_BY (CHILD, PARENT) values (11, 18); insert into TEST_CONNECT_BY (CHILD, PARENT) values (7, 18); insert into TEST_CONNECT_BY (CHILD, PARENT) values (38, null); insert into TEST_CONNECT_BY (CHILD, PARENT) values (26, null); insert into TEST_CONNECT_BY (CHILD, PARENT) values (18, null); commit; 1)常规的查询(start with .. connect by prior..) SQL> select child from test_connect_by 2 start with parent is null 3 connect by prior child=parent; CHILD ---------- 38 15 10 5 2 3 17 9 8 6 26 13 1 12 18 11 7 17 rows selected 这样的层次不分明，要利用level. SQL> select lpad(' ',4*(level-1))||child child 2 from test_connect_by 3 start with parent is null 4 connect by prior child=parent; CHILD -------------------------------------------------------------------------------- 38 15 10 5 2 3 17 9 8 6 26 13 1 12 18 11 7 17 rows selected 当然也可以写成： SQL> select lpad(' ',4*(level-1))||child child 2 from test_connect_by 3 start with parent is null 4 connect by parent=prior child; CHILD -------------------------------------------------------------------------------- 38 15 10 5 2 3 17 9 8 6 26 13 1 12 18 11 7 17 rows selected prior在 '='左右都可以，只要记住prior那边的列作为父列，另一边的列作为子列就行了。例如刚才的例子： SQL> select lpad(' ',4*(level-1))||child child 2 from test_connect_by 3 start with parent is null 4 connect by parent=prior child; 和 SQL> select lpad(' ',4*(level-1))||child child 2 from test_connect_by 3 start with parent is null 4 connect by prior child=parent; 都是以child作为父列去找到相关的记录，所以两个语句的查询结果是一样的。如果以parent作为父列，那结果明显不一样了。 SQL> select lpad(' ',4*(level-1))||child child 2 from test_connect_by 3 start with parent is null 4 connect by prior parent=child; CHILD -------------------------------------------------------------------------------- 18 26 38 这样查询出来的就只有一层，这样的数据好像没什么现实意义，一般情况应该是树型数据要显示为从leaf到root这样的显示才会以parent作为父列。例如从叶子2开始查询： SQL> select lpad(' ',4*(level-1))||child 2 from test_connect_by 3 start with child=2 4 connect by prior parent=child; LPAD('',4*(LEVEL-1))||CHILD -------------------------------------------------------------------------------- 2 5 15 38

2)筛选某个结点或分支，如果是筛选某个结点不显示则在添加where+条件,如果是筛选分支，则在connect by后面加条件。 例如筛选child为5的结点不显示： SQL> select lpad(' ',4*(level-1))||child child 2 2 from test_connect_by 3 where child!=5 4 start with parent is null 5 connect by prior child=parent; CHILD -------------------------------------------------------------------------------- 38 15 10 2 3 17 9 8 6 26 13 1 12 18 11 7 16 rows selected 如果是筛选child为5这个树枝都不显示，则是： SQL> select lpad(' ',4*(level-1))||child child 2 from test_connect_by 3 start with parent is null 4 connect by prior child=parent 5 and child!=5; CHILD -------------------------------------------------------------------------------- 38 15 10 17 9 8 6 26 13 1 12 18 11 7 14 rows selected

3）prior+column,这个是显示某个结点的直接父结点，例如： SQL> select level,lpad(' ',4*(level-1))||child child,prior child "parent" 2 from test_connect_by 3 start with parent is null 4 connect by prior child=parent; LEVEL CHILD parent ---------- -------------------------------------------------------------------------------- ---------- 1 38 2 15 38 3 10 15 3 5 15 4 2 5 4 3 5 2 17 38 3 9 17 3 8 17 2 6 38 1 26 2 13 26 2 1 26 2 12 26 1 18 2 11 18 2 7 18 17 rows selected

4）sys_connect_by_path 显示父结点到当前结点的路径，例如： SQL> select lpad(' ',4*(level-1))||child child,sys_connect_by_path(child,'|') path 2 from test_connect_by 3 3 start with parent is null 4 connect by prior child=parent; CHILD PATH -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 38 |38 15 |38|15 10 |38|15|10 5 |38|15|5 2 |38|15|5|2 3 |38|15|5|3 17 |38|17 9 |38|17|9 8 |38|17|8 6 |38|6 26 |26 13 |26|13 1 |26|1 12 |26|12 18 |18 11 |18|11 7 |18|7 17 rows selected 5）connect_by_root 显示结点的根结点，例如: SQL> select lpad(' ',4*(level-1))||child child,connect_by_root child "root" 2 from test_connect_by 3 3 start with parent is null 4 connect by prior child=parent; CHILD root -------------------------------------------------------------------------------- ---------- 38 38 15 38 10 38 5 38 2 38 3 38 17 38 9 38 8 38 6 38 26 26 13 26 1 26 12 26 18 18 11 18 7 18 17 rows selected

6) connect_by_isleaf 判断当前结点是否是叶子，如是则值为1，否则为0.例如: SQL> select lpad(' ',4*(level-1))||child child,connect_by_isleaf "isleaf" 2 from test_connect_by 3 start with parent is null 4 connect by prior child=parent; CHILD isleaf -------------------------------------------------------------------------------- ---------- 38 0 15 0 10 1 5 0 2 1 3 1 17 0 9 1 8 1 6 1 26 0 13 1 1 1 12 1 18 0 11 1 7 1 17 rows selected

在这里要注意个问题，当要筛选level的时候，条件的位置不同则结果是不一样的。请看下面两个例子： SQL> select level,lpad(' ',4*(level-1))||child,connect_by_isleaf "isleaf" 2 from test_connect_by 3 start with parent is null 4 connect by prior child=parent and level<=3; LEVEL LPAD('',4*(LEVEL-1))||CHILD isleaf ---------- -------------------------------------------------------------------------------- ---------- 1 38 0 2 15 0 3 10 1 3 5 1 2 17 0 3 9 1 3 8 1 2 6 1 1 26 0 2 13 1 2 1 1 2 12 1 1 18 0 2 11 1 2 7 1 15 rows selected 这个结果很正常，所有叶子结点的isleaf都是为1。 再看下面的例子： SQL> select level,lpad(' ',4*(level-1))||child,connect_by_isleaf "isleaf" 2 from test_connect_by 3 where level<=3 4 start with parent is null 5 connect by prior child=parent; LEVEL LPAD('',4*(LEVEL-1))||CHILD isleaf ---------- -------------------------------------------------------------------------------- ---------- 1 38 0 2 15 0 3 10 1 3 5 0 2 17 0 3 9 1 3 8 1 2 6 1 1 26 0 2 13 1 2 1 1 2 12 1 1 18 0 2 11 1 2 7 1 15 rows selected 有没有发现5好像是叶子结点呀，为什么isleaf是0呢?原因是该语句判断一个结点是否是叶子是根据 SQL> select level,lpad(' ',4*(level-1))||child,connect_by_isleaf "isleaf" 2 from test_connect_by 3 start with parent is null 4connect by prior child=parent; 所生成的树型来作判断的，而不是根据level<=3筛选出的树结构来作判断的。 7）order siblings by 用这个语句来对树形数据来不会改变原来的树结构，只是根据是ASC或是DESC来调整下结构的位置而已。 SQL> select lpad(' ',4*(level-1))||child "child" 2 from test_connect_by 3 3 start with parent is null 4 connect by prior child=parent 5 order siblings by child 6 desc; child -------------------------------------------------------------------------------- 38 17 9 8 15 10 5 3 2 6 26 13 12 1 18 11 7 17 rows selected 如果是用order by 的话则原来的结构就会被打乱了。 SQL> select lpad(' ',4*(level-1))||child child 2 from test_connect_by 3 connect by prior child=parent 4 start with parent is null 5 order by child; CHILD -------------------------------------------------------------------------------- 2 3 10 5 8 9 1 11 12 13 15 17 6 7 18 26 38 17 rows selected