connect by的简单用法

cubu8303

于 2011-03-17 16:32:36 发布

174

一、首先从一个经典的查询序列数例子入手：

    select level   from dual connect by level <= 10

    select level   from dual connect by 1 = 1
    上例均可查询得到1 .. N 的序列(但最多100行)

    我们来分析一下其工作原理，level<=10用来控制循环的次数，即要重复多少次扫描表dual中的内容。第一次扫描得出的结果集的level都是1,第二次扫描的结果集的level都是2,依此类推。可能用文字描述的不太容易懂，下面我们通过试验来说明：

    with x as

    ( select 'aa' chr from dual

    union all

    select 'bb' chr from dual)

    select level ,chr,lpad( ' ' ,( level - 1 )* 5 , '-' )||chr other from x connect by level <= 3

    LEVEL CHR    OTHER
    1     aa     aa
    2     aa     ---- aa
    3     aa     --------- aa
    3     bb     --------- bb
    2     bb     ---- bb
    3     aa     --------- aa
    3     bb     --------- bb
    1     bb     bb
    2     aa     ---- aa
    3     aa     --------- aa
    3     bb     --------- bb
    2     bb     ---- bb
    3     aa     --------- aa
    3     bb     --------- bb
    可见是全部level的树形结构，当扫描对象是dual时，即一个level只生成一条记录.

二、如何解决from dual只显示100行的问题：
    selectlevel from dual connectbylevel <=300
    只显示100行，但据说只是9i的显示问题，解决方法如下：
    select * from (level from dual connectbylevel <=300)
    即可显示300行！用以解决无法多行显示问题

    应用举例如下：

    select to_date( 2008 || '0101' , 'yyyymmdd' ) + rownum - 1 rq,

    to_char(to_date( 2008 || '0101' , 'yyyymmdd' ) + rownum - 1 , 'day' ) day

    from ( select rownum from dual
    connect by rownum <= to_date( 2008 || '1231' , 'yyyymmdd' ) - to_date( 2008 || '0101' , 'yyyymmdd' )+ 1 );
    作用：列出所有日期及星期几，可用于查询工作日

三、start with ... connect by 用法讲解：
    构建如下table：
    ID   NAME PID
    1    10    0
    2    11    1
    3    20    0
    4    12    1
    5    121   2
    code example1：

    select TBL_TEST.*, level from TBL_TEST

    start with pid= 1 -- 可写到 connect by 后面

    connect by prior pid = id

    ID   NAME PID LEVEL
    2    11    1    1
    1    10    0    2
    4    12    1    1
    1    10    0    2

    code example2：

    select TBL_TEST.*, level from TBL_TEST

    start with id = 5 -- 可写到 connect by 后面

    connect by prior pid = id

    ID   NAME PID LEVEL
    5     121    2    1
    2     11     1    2
    1     10     0    3

    说明：
    1、先从start with pid=1 句开始查询得到 2   11   1   1 =====> level置1；
    2、根据pid = id，查询 id=1 句，得到 1   10   0   2 =====> level置2；
    3、根据pid = id，查询 id=0 句，未查询到后结束该树枝；

    注：prior pid = id 句说明 pid是id的父节点，通过pid查询id

四、sys_connect_by_path函数讲解：

    sys_connect_by_path函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分，在一个格中显示

    select level ,sys_connect_by_path( id , '/' ) from TBL_TEST

    start with pid= 1

    connect by prior pid = id ;

    select level ,sys_connect_by_path(pid, '/' ) from TBL_TEST

    start with pid= 1

    connect by prior pid = id ;

    可以比较这两段代码的运行结果与code example1的结果之间的差异，即可理解此函数用法。

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

转载于:http://blog.itpub.net/24387280/viewspace-689788/