利用connect by顯示類目層次關係

tengrid發表於2009-05-18

相關貼子參見:
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


start with ...connect by的完整語法解決參見<>

實際上,connect by具有where的作用,不過它不是針對二維的關係,而是層次關係
SQL> select * from dual where 1=1;

X

SQL> select * from dual connect by 1=1;   

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)的所有關係.


所以，上述規則可以描述為：
1) 當運算子prior在parent前面時,得到child =>parent關係樹
    當prior在child前面時，得到parent =>child關係樹
2) 當指定了start with時，過濾掉沒有包含指定欄位的所有關係;否則，不作任何過濾,顯示所有關係

 

start with ..connect by顯示樹狀關係結構的特點可以應用在類目層次關係的顯示上.

類目的關係層次如下:
SQL> desc BossCommodityClass
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CLASSID                                            NUMBER
 PARENTID                                           NUMBER
 CLASSNAME                                        VARCHAR2(100)
 


col classid  for 999999999
col classname for a15
col tree for a120
set linesize 1000
set pagesize 1000
set head off
select * from
(
select CLASSID,CLASSNAME,level,sys_connect_by_path(CLASSID||CLASSNAME,'--&gt') tree
 from BossCommodityClass
 start with classid=24501
 connect by prior CLASSID=PARENTID
 order by level desc
 )
 where rownum<=20;

    24503 愛情/文藝                3 --&gt24501電影/電視/音樂/曲藝--&gt24502歐美影劇DVD--&gt24503愛情/文藝
     24504 動作/冒險                3 --&gt24501電影/電視/音樂/曲藝--&gt24502歐美影劇DVD--&gt24504動作/冒險
     24505 獲獎/經典                3 --&gt24501電影/電視/音樂/曲藝--&gt24502歐美影劇DVD--&gt24505獲獎/經典
     24506 恐怖/驚悚                3 --&gt24501電影/電視/音樂/曲藝--&gt24502歐美影劇DVD--&gt24506恐怖/驚悚
.................

如何實現將各個parentid 以column的形式展現?

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/94384/viewspace-600302/，如需轉載，請註明出處，否則將追究法律責任。

相關文章