ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 利用connect by显示类目层次关系

利用connect by显示类目层次关系

原创 Linux操作系统 作者:tengrid 时间:2009-05-18 19:19:02 0 删除 编辑

相关贴子参见:
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)的所有关系.


所以,上述规则可以描述为:
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,'-->') tree
 from BossCommodityClass
 start with classid=24501
 connect by prior CLASSID=PARENTID
 order by level desc
 )
 where rownum<=20;

    24503 爱情/文艺                3 -->24501电影/电视/音乐/曲艺-->24502欧美影剧DVD-->24503爱情/文艺
     24504 动作/冒险                3 -->24501电影/电视/音乐/曲艺-->24502欧美影剧DVD-->24504动作/冒险
     24505 获奖/经典                3 -->24501电影/电视/音乐/曲艺-->24502欧美影剧DVD-->24505获奖/经典
     24506 恐怖/惊悚                3 -->24501电影/电视/音乐/曲艺-->24502欧美影剧DVD-->24506恐怖/惊悚
.................

如何实现将各个parentid 以column的形式展现?

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/94384/viewspace-600302/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-05-18

  • 博文量
    136
  • 访问量
    378235