一个树形聚集SQL问题（二）

看到ITPUB上一个帖子，感觉楼主的需要比较有意思，于是尝试了一下。问题源自：http://www.itpub.net/thread-1020586-1-1.html

上一篇给出了一个SQL实现，不过由于不是很清楚楼主的含义，加上测试数据过于简单，没有将问题完全展现，因此第一篇给出的SQL并不能完全满足需要。这篇根据新的测试数据来构造求解的SQL。

一个树形聚集SQL问题（一）：http://yangtingkun.itpub.net/post/468/466388

虽然上一篇给出的SQL能得到正确的结果，但是真正的情况比测试中要复杂很多，树形的分叉并非只可能存在于第一层，而是在任一层都可能包括多个叶节点。

比如插入一条记录：

SQL> SELECT * FROM T_LEVEL;

    LEVELS PARENT     CHILD      PARENT_QTY  CHILD_QTYITPUB个人空间R.f!utVb(B:M
---------- ---------- ---------- ---------- ----------ITPUB个人空间-MT0D&}x(ys
        1 A          B                   1          3
jN3_5E#A_6d0         2 B          C                   2          3ITPUB个人空间 Am;Na1q+Bx+e
        3 C          D                   5          6
|B7]U:{0w0         4 D          E                   1          2ITPUB个人空间8cf` DMQ0RKS
        1 A          Z                   1          3

SQL> INSERT INTO T_LEVEL VALUES (4, 'D', 'F', 1, 3);

已创建1行。

SQL> SELECT P,
7POO`"K'U}0  2   SUBSTR(MAX(D), LENGTH(MAX(D))) C,ITPUB个人空间$fP_t`4n2e
  3   POWER(10, SUM(LOG(10, Q))) QITPUB个人空间;y#^6hq.y2n8x XA
  4  FROMITPUB个人空间T*G"S\:|(ZME
  5  (ITPUB个人空间GA'~#mNk
  6  SELECT CONNECT_BY_ROOT(PARENT) P,
E{H C9P)S0  7   SYS_CONNECT_BY_PATH(CHILD, '/') D,
"\?e`*? wf:X ]i2j0  8   CHILD_QTY/PARENT_QTY QITPUB个人空间ckrPr
I0T
  9  FROM T_LEVEL
(J;ll
n P8I1~0 10  START WITH LEVELS = 1ITPUB个人空间^'MDJ Y u-\9W
 11  CONNECT BY PRIOR CHILD = PARENT
4[0e }"Q9J,P z0 12  )
r k+s7v;z!OO
E'Qd9W0 13  GROUP BY P, SUBSTR(D, 2, 1);

P          C                   QITPUB个人空间3qc Bnk
l
---------- ---------- ----------
8` Fi~+W0A          F               32.4ITPUB个人空间p Y@S3ga(U.h
A          Z                   3

现在得到的结果显然是不正确的，这里应该得到3条记录，A到E、A到F和A到Z。

虽然SQL得到的结果是不正确的，不过解题的思想并没有变，仍然是通过SUM解决连乘问题，通过构造来获取聚集的GROUP BY字段。

为了简单这个问题，可以将树形查询反过来进行，从所有的叶节点出发，汇总数据到根节点，具体实现SQL如下：

SQL> SELECTITPUB个人空间c7`h|1P%{&S,d
  2   SUBSTR(MAX(D), INSTR(MAX(D), '/', -1) + 1) C,
!\X1Z&tp0  3   P,ITPUB个人空间3R%T*m3a\K2] |!H\
  4   POWER(10, SUM(LOG(10, Q))) QITPUB个人空间p*p
yAe] hF
  5  FROM
Dt)l(w8F(u+^ ~0  6  (ITPUB个人空间F6WG"yH)} @@(J~
  7   SELECT CONNECT_BY_ROOT(CHILD) P,
6_v.N9a8[u8T(v0  8    SYS_CONNECT_BY_PATH(PARENT, '/') D,
~ t| pCir0  9    CHILD_QTY/PARENT_QTY QITPUB个人空间}6\:_3^ }2P$A:wV{
 10   FROM T_LEVELITPUB个人空间{FB/@|9U(x[
 11   START WITH CHILD IN
a$vh
v)k9j'v4w1?^s3B/R0 12   (ITPUB个人空间1Z%I7o4pyl.C
 13    SELECT CHILD FROM
?-KV&CYyqr0 14    (ITPUB个人空间 FTPjD5x,KU2p
c
 15     SELECT CHILD, CONNECT_BY_ISLEAF LEAF FROM T_LEVEL
ZM C.e(VLJH&e0 16     START WITH LEVELS = 1ITPUB个人空间"kzn l#D9g.u.qh
s
 17     CONNECT BY PRIOR CHILD = PARENT
c2M|*G
lX0 18    )ITPUB个人空间 RS7DaM\"W-B#[s
 19    WHERE LEAF = 1ITPUB个人空间@;{$Y+W4} ae&}qNn
 20   )ITPUB个人空间$l{0oE8oq\i
 21   CONNECT BY PRIOR PARENT = CHILD
k:f%sx/@(Sf0 22  )ITPUB个人空间
BT8~;\3|
 23  GROUP BY P
5_"xq L/K,B0b)Q4px0 24  ;

C                              P                                       QITPUB个人空间&R1eue{ wX-\/p n
------------------------------ ------------------------------ ----------
5e+^ s%J2@$s(V[0A                              Z                                      3ITPUB个人空间r R;n7Cl3FWR'M d;?
A                              E                                   10.8ITPUB个人空间"v$s-a:il#j1P)O
A                              F                                    16.2

这种方式仍然是10g的方法，因为使用了CONNECT_BY_ISLEAF伪列，而这个伪列是10g的新特性，由来标识当前的记录是否是叶节点。

如果在9i中，则需要使用别的方法来实现这个功能，实现的SQL如下：

SQL> SELECT
,Hu'HPI0  2   SUBSTR(MAX(D), INSTR(MAX(D), '/', -1) + 1) C,
8Ss qQ1H%N!`5R0  3   SUBSTR(P, 2, INSTR(P, '/', 1, 2) - 2) P,ITPUB个人空间kU{8}(|6nK1h
  4   POWER(10, SUM(LOG(10, Q))) QITPUB个人空间eT5D2q;C/j[Hw;J
  5  FROM
^$} X+l
w8?0  6  (
y*G2r!Y F7^*j0  7   SELECT SYS_CONNECT_BY_PATH(CHILD, '/') || '/' P,ITPUB个人空间-p/h{)BU6x#e L
  8    SYS_CONNECT_BY_PATH(PARENT, '/') D,ITPUB个人空间]] L*B+wYa1}9I
  9    CHILD_QTY/PARENT_QTY QITPUB个人空间k*E/Ak v
 10   FROM T_LEVEL
t4RPI!ybt0 11   START WITH CHILD INITPUB个人空间.vh7K
}.d L[%]
 12   (
3T l.r(vmij}9u+i$j0 13    SELECT CHILD FROM
\]f6C;|!x!s+sh0 14    (
*q?e9C@#S0 15     SELECT CHILD,ITPUB个人空间8]} qz(T}-\
b1J
 16      CASE WHEN LEAD(LEVELS) OVER(ORDER BY ID) > LEVELS THEN 0 ELSE 1 END LEAF
~e
@'Mu6L0 17     FROMITPUB个人空间:D%E;}0V*_-W
 18     (ITPUB个人空间T N!C` rR ]
 19      SELECT ROWNUM ID, LEVELS, PARENT, CHILD
0lw*\0zsh5^Dx0 20      FROM T_LEVEL
n(b1`a#Rr0 21      START WITH LEVELS = 1ITPUB个人空间ux9a~Z
 22      CONNECT BY PRIOR CHILD = PARENT
c9J2N0a MY0 23      ORDER SIBLINGS BY LEVELSITPUB个人空间MJ7uVG
 24     )ITPUB个人空间)N!b I2`:l1o$W
 25    )ITPUB个人空间4^.sh`#B9T4RDl
 26    WHERE LEAF = 1
!q1I?"qAJ(R{/rD)b0 27   )ITPUB个人空间A|(DZE!q
 28   CONNECT BY PRIOR PARENT = CHILD
5C0LK0|-}0 29  )
6YpLHW+mI` ewX)_x0 30  GROUP BY SUBSTR(P, 2, INSTR(P, '/', 1, 2) - 2);

C                              P                                       Q
1x7~ph1rz`\0------------------------------ ------------------------------ ----------ITPUB个人空间 G.r,C{[1V{3V
A                              Z                                      3
`[
W0c;xL,Z0A                              E                                   10.8
yK"k1~ G d(z*P0A                              F                                    16.2