相关贴子参见:
http://www.itpub.net/showthread.php?s=&threadid=712131&highlight=connect+and+by
http://www.itpub.net/716770.html
http://blog.csdn.net/pangpangde/archive/2006/07/24/970345.aspx
http://www.javalife.cn/javaforum/viewthread?thread=118

start with ...connect by的完整语法解决参见<>

实际上,connect by具有where的作用,不过它不是针对二维的关系,而是层次关系
SQL> select * from dual where 1=1;

X

SQL> select * from dual connect by 1=1;    <----- connect by 1=1为什么产生一个无限loop?

X
X
X
X
X
^C
SQL>

上述connect by 1=1的语句取自 yong huang的一个例子.

-------------------------------------quote begin--------------------------------------------
在SELECT命令中使用CONNECT BY 和START WITH 子句可以查詢表中的樹型結構關係。其命令格式如下：
SELECT …..
CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 裂名2}
[START WITH]；
其中：CONNECT BY子句說明每行資料將是按層次順序檢索，並規定將表中的資料連入樹型結構的關係中。

PRIORY運算符必須放置在連接關係的兩列中某一個的前面。對於節點間的父子關係，PRIOR運算符在一側表示父節點，

在另一側表示子節點，從而確定查找樹結構是的順序是自頂向下還是自底向上。在連接關係中，除了可以使用列名外，

還允許使用列運算式。START WITH 子句為可選項，用來標識哪個節點作為查找樹型結構的根節點。

若該子句被省略，則表示所有滿足查詢條件的行作為根節點。
-------------------------------------quote end--------------------------------------------


从child到parent的例子
create table test1(empno number,ename varchar2(30),managerid number);
insert into test1 values(10000,'martin',0);
insert into test1 values(10001,'binn',10000);
insert into test1 values(10002,'davit',10001);
insert into test1 values(10003,'kyte',10002);
insert into test1 values(10004,'shre',10003);
insert into test1 values(10006,'ivan',10002);
insert into test1 values(10007,'richard',10003);

col router for a50
col ename for a10
set linesize 1000
       select empno,ename,managerid,level,sys_connect_by_path(empno||ename,'->') router
         from test1
        start with ename='richard'
        connect by empno=prior managerid
       order by level desc;

     EMPNO ENAME       MANAGERID      LEVEL ROUTER
---------- ---------- ---------- ---------- --------------------------------------------------
     10000 martin              0          5 ->10007richard->10003kyte->10002davit->10001binn->
                                            10000martin

     10001 binn            10000          4 ->10007richard->10003kyte->10002davit->10001binn
     10002 davit           10001          3 ->10007richard->10003kyte->10002davit
     10003 kyte            10002          2 ->10007richard->10003kyte
     10007 richard         10003          1 ->10007richard

可见,由于start with 'richard',它只列出了自richard开始(以它为child leaf)的所有层次关系,由于'ivan'属于另一个分支,故不在结果集中. 如果省略start with,则会拿出包括'ivan'在内的所有层次关系,如下:
SQL> set head off
SQL> set pagesize 1000
SQL>        select empno,ename,managerid,level,sys_connect_by_path(empno||ename,'->') router
         from test1
        --start with ename='richard'
        connect by empno=prior managerid
       order by level desc;

     10000 martin              0          5 ->10007richard->10003kyte->10002davit->10001binn->
                                            10000martin

     10000 martin              0          5 ->10004shre->10003kyte->10002davit->10001binn->100
                                            00martin

     10000 martin              0          4 ->10003kyte->10002davit->10001binn->10000martin
     10000 martin              0          4 ->10006ivan->10002davit->10001binn->10000martin
     10001 binn            10000          4 ->10007richard->10003kyte->10002davit->10001binn
     10001 binn            10000          4 ->10004shre->10003kyte->10002davit->10001binn
     10001 binn            10000          3 ->10003kyte->10002davit->10001binn
     10001 binn            10000          3 ->10006ivan->10002davit->10001binn
     10002 davit           10001          3 ->10007richard->10003kyte->10002davit
     10002 davit           10001          3 ->10004shre->10003kyte->10002davit
     10000 martin              0          3 ->10002davit->10001binn->10000martin
     10002 davit           10001          2 ->10003kyte->10002davit
     10001 binn            10000          2 ->10002davit->10001binn
     10000 martin              0          2 ->10001binn->10000martin
     10003 kyte            10002          2 ->10004shre->10003kyte
     10003 kyte            10002          2 ->10007richard->10003kyte
     10002 davit           10001          2 ->10006ivan->10002davit
     10003 kyte            10002          1 ->10003kyte
     10002 davit           10001          1 ->10002davit
     10007 richard         10003          1 ->10007richard
     10000 martin              0          1 ->10000martin
     10001 binn            10000          1 ->10001binn
     10006 ivan            10002          1 ->10006ivan
     10004 shre            10003          1 ->10004shre

已选择24行。

每次scan test1上的一行时，都会拿到出所有empno的层次关系(from child to parent, current empno is the parent).

SQL>        select empno,ename,managerid,level,sys_connect_by_path(empno||ename,'->') router
  2           from test1
  3          start with ename='davit'
  4          connect by prior empno= managerid
  5         order by level desc;

     10004 shre            10003          3 ->10002davit->10003kyte->10004shre
     10007 richard         10003          3 ->10002davit->10003kyte->10007richard
     10006 ivan            10002          2 ->10002davit->10006ivan
     10003 kyte            10002          2 ->10002davit->10003kyte
     10002 davit           10001          1 ->10002davit

这次,prior在child column前,所以,结果是以start with指定的'davit'开始(以它为parent root)的所有关系.