connect by 的用法

一、首先从一个经典的查询序列数例子入手： ITPUB个人空间Y{1V:K{

#EpLB!m#p6?8^0    select level   from dual connect by level <= 10 ITPUB个人空间vhNM6Y3_t0w

%S K#jE.{(j0    select level   from dual connect by 1 = 1 ITPUB个人空间)y,t qwIJ
    上例均可查询得到1 .. N 的序列(但最多100行)
x+H;sX)jN0 
edw:G9x[$D"u.uS}y0    我们来分析一下其工作原理，level<=10用来控制循环的次数，即要重复多少次扫描表dual中的内容。第一次扫描得出的结果集的level都是1,第二次扫描的结果集的level都是2,依此类推。可能用文字描述的不太容易懂，下面我们通过试验来说明： ITPUB个人空间2ln2{-nR
ITPUB个人空间GG}2B {
    with x as ITPUB个人空间(Y!^:gO7LTO}

D HUE P"~'Q0    ( select 'aa' chr from dual
F0~hwy#|] vQ7x+m0
}@ [(Jg0    union all
&n;bu\] vB0 ITPUB个人空间(vc'`d?.qJ6G
    select 'bb' chr from dual) ITPUB个人空间SE,_@$DQCV)Z
ITPUB个人空间Lsj1X V,IvE#eV/R
    select level ,chr,lpad( ' ' ,( level - 1 )* 5 , '-' )||chr other from x connect by level <= 3
g!D&g2z4}5T0 ITPUB个人空间F*L7`[ x5Q
    LEVEL CHR    OTHER ITPUB个人空间5f J-@ RW0H
    1     aa     aa ITPUB个人空间2Z["m ?x"f-p+fR
    2     aa     ---- aa
ggj M*FTg;B0    3     aa     --------- aa ITPUB个人空间 G\'?t)Vut&C2EAo
    3     bb     --------- bb ITPUB个人空间L FJ2I jb@3{3^1?&@
    2     bb     ---- bb ITPUB个人空间7ryKCeSc
    3     aa     --------- aa
(]:u%IxIJ%L himKA0    3     bb     --------- bb ITPUB个人空间J4tZ}7U4w C?-e
    1     bb     bb
z)gQ;IJ1M0    2     aa     ---- aa
f X%B`nLz@"O!l0    3     aa     --------- aa ITPUB个人空间iD&dIJ
    3     bb     --------- bb ITPUB个人空间c2g/NE7u H
    2     bb     ---- bb ITPUB个人空间5Rl.`6O(W
    3     aa     --------- aa
U w6d;B1mp0    3     bb     --------- bb
t M3]KYJ3D g0    可见是全部level的树形结构，当扫描对象是dual时，即一个level只生成一条记录.
KE UApc%[/J.J0  ITPUB个人空间^Ko)FM!C#| H
二、如何解决from dual只显示100行的问题： ITPUB个人空间 xmH,}}4td
    selectlevel from dual connectbylevel <=300 
R z0Tg"T/V?Z0    只显示100行，但据说只是9i的显示问题，解决方法如下：
xu*p-Y$I Z0    select * from (level from dual connectbylevel <=300) ITPUB个人空间0sfz;_Q(A
    即可显示300行！用以解决无法多行显示问题
(s!w:?L9KY/Kvd0 
~.` d)ou+v6~:j5us0    应用举例如下：
{+u'qXmkaX0 ITPUB个人空间 g {1Hj6bR2Q
    select to_date( 2008 || '0101' , 'yyyymmdd' ) + rownum - 1 rq, ITPUB个人空间&I]&H!ch2u

Yi5\g+`:x4^0    to_char(to_date( 2008 || '0101' , 'yyyymmdd' ) + rownum - 1 , 'day' ) day ITPUB个人空间3P8Sh;?OuL&d

'\7wF,jut1R0    from ( select rownum from dual
E9}2H6t9LJ2WY9k9mi0    connect by rownum <= to_date( 2008 || '1231' , 'yyyymmdd' ) - to_date( 2008 || '0101' , 'yyyymmdd' )+ 1 ); ITPUB个人空间$R+Cya-Qy)r
    作用：列出所有日期及星期几，可用于查询工作日
*pPg6?-|e~0  ITPUB个人空间 zH3?} S(J \'w*b
 
ZqFl)A8ZF6d\0  ITPUB个人空间~-y,r#{:NF"fi
三、start with ... connect by 用法讲解： ITPUB个人空间0F'{m rU@Dw
    构建如下table： ITPUB个人空间q5]VU'Jhe1e9sE-y
    ID   NAME  PID ITPUB个人空间M+e#|Z b G(EP&^Q
    1    10    0
L7FJ7dzy0    2    11    1
!|uM0J}H0    3    20    0
]}PYI9}0    4    12    1 ITPUB个人空间X't2HjXD+x$N+]
    5    121   2 ITPUB个人空间 xU/MF9q,p
    code example1： ITPUB个人空间]4X I+Pl{v Z

"C)c Y`/GH0    select TBL_TEST.*, level from TBL_TEST
u[ o9xUy`x0 ITPUB个人空间&ujV$AJW h B
    start with pid= 1 -- 可写到 connect by 后面
LXIu#]3D9F2q0
G%\FBr/e0    connect by prior pid = id ITPUB个人空间)iBP(O"{.P

yW9] rH6K.N5hV-X0    ID   NAME PID LEVEL ITPUB个人空间jfCht1?8Y
    2    11    1    1 ITPUB个人空间v5lQHKAS+O%Z
    1    10    0    2 ITPUB个人空间m2r*w$w1q6{(\/e#q
    4    12    1    1 ITPUB个人空间(M[5vn-eYO
    1    10    0    2 ITPUB个人空间,N2B1{9o8G

2I5i_Tr W0    code example2： ITPUB个人空间 n-o!d?!Y.z Rk1g#}

*\,L}1?;PoZN0    select TBL_TEST.*, level from TBL_TEST
"V n Eq$y%ss0
G!CBg8nUHo!kG0    start with id = 5 -- 可写到 connect by 后面 ITPUB个人空间 Z:zJ xLU ]/nZ

](L5u YpW2gms[0    connect by prior pid = id ITPUB个人空间$G2M:X*}2k Y5k'?
ITPUB个人空间5k _"~ ` g s'lpg@9J
    ID   NAME PID LEVEL ITPUB个人空间%uY"z5u0CR
    5     121    2    1
0d5V4T~lx!H0    2     11     1    2 ITPUB个人空间;e}jL2C'o \{~~
    1     10     0    3 ITPUB个人空间?,dGQ3q"ySZ
 
j}O+Tc0    说明：
(VIN.m:~0    1、先从start with pid=1 句开始查询 得到 2   11   1   1 =====> level置1；
HJn4F+@-t-s0    2、根据pid = id，查询 id=1 句，得到 1   10   0   2 =====> level置2；
E3u6fY/s P2g A0    3、根据pid = id，查询 id=0 句，未查询到后结束该树枝；
X/m+m$yn1U0
[Sy f+o3{0    注：prior pid = id 句说明 pid是id的父节点，通过pid查询id
K4wcO/}Q"{0
2]:@*puL?%jC0 
2hi uXkZ@0 ITPUB个人空间1T6VkkV&zH
四、sys_connect_by_path函数讲解：
jWt \8Q0 ITPUB个人空间/B UbGEv$f
    sys_connect_by_path函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分，在一个格中显示
}'NY3j}V+f-]U0
t.P;N8W7_:T@v0    select level ,sys_connect_by_path( id , '/' ) from TBL_TEST
4V~ _A.K7X$B0
(lum2P~#`jI8Q0    start with pid= 1 ITPUB个人空间Q%F@Zew
ITPUB个人空间0@ K$d4Zo"^DH4[-F
    connect by prior pid = id ; ITPUB个人空间'fzWW,~'alk)_H
ITPUB个人空间)d$u @em/| ?(L
  ITPUB个人空间-nXL!d.bT^
ITPUB个人空间K8`.b'W%R
    select level ,sys_connect_by_path(pid, '/' ) from TBL_TEST
|R:V0oMu0
4S | dan*dS0    start with pid= 1 ITPUB个人空间-[Z;C6z4C&~X

8p Oz o VJC0    connect by prior pid = id ; ITPUB个人空间,u$T@U$a] O

/cNJ;qkH0cC0ig4`0  ITPUB个人空间}SXW2qKY^
ITPUB个人空间%zT,^}6~}
    可以比较这两段代码的运行结果与code example1的结果之间的差异，即可理解此函数用法。

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

转载于:http://blog.itpub.net/25472150/viewspace-690109/