一個樹形聚集SQL問題（二） (物料BOM消耗量計算) zt

上一篇給出了一個SQL實現，不過由於不是很清楚樓主的含義，加上測試資料過於簡單，沒有將問題完全展現，因此第一篇給出的SQL並不能完全滿足需要。這篇根據新的測試資料來構造求解的SQL。

一個樹形聚集SQL問題（一）：http://yangtingkun.itpub.net/post/468/466388

雖然上一篇給出的SQL能得到正確的結果，但是真正的情況比測試中要複雜很多，樹形的分叉並非只可能存在於第一層，而是在任一層都可能包括多個葉節點。

比如插入一條記錄：

SQL> SELECT * FROM T_LEVEL;

LEVELS PARENT CHILD PARENT_QTY CHILD_QTY
---------- ---------- ---------- ---------- ----------
1 A B 1 3
2 B C 2 3
3 C D 5 6
4 D E 1 2
1 A Z 1 3

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

已建立 1 行。

SQL> SELECT P,
2 SUBSTR(MAX(D), LENGTH(MAX(D))) C,
3 POWER(10, SUM(LOG(10, Q))) Q
4 FROM
5 (
6 SELECT CONNECT_BY_ROOT(PARENT) P,
7 SYS_CONNECT_BY_PATH(CHILD, '/') D,
8 CHILD_QTY/PARENT_QTY Q
9 FROM T_LEVEL
10 START WITH LEVELS = 1
11 CONNECT BY PRIOR CHILD = PARENT
12 )
13 GROUP BY P, SUBSTR(D, 2, 1);

P C Q
---------- ---------- ----------
A F 32.4A Z 3

現在得到的結果顯然是不正確的，這裡應該得到3條記錄，A到E、A到F和A到Z。

雖然SQL得到的結果是不正確的，不過解題的思想並沒有變，仍然是透過SUM解決連乘問題，透過構造來獲取聚集的GROUP BY欄位。

為了簡單這個問題，可以將樹形查詢反過來進行，從所有的葉節點出發，彙總資料到根節點，具體實現SQL如下：

SQL> SELECT
2 SUBSTR(MAX(D), INSTR(MAX(D), '/', -1) + 1) C,
3 P,
4 POWER(10, SUM(LOG(10, Q))) Q
5 FROM
6 (
7 SELECT CONNECT_BY_ROOT(CHILD) P,
8 SYS_CONNECT_BY_PATH(PARENT, '/') D,
9 CHILD_QTY/PARENT_QTY Q
10 FROM T_LEVEL
11 START WITH CHILD IN
12 (
13 SELECT CHILD FROM
14 (
15 SELECT CHILD, CONNECT_BY_ISLEAF LEAF FROM T_LEVEL
16 START WITH LEVELS = 1
17 CONNECT BY PRIOR CHILD = PARENT
18 )
19 WHERE LEAF = 1
20 )
21 CONNECT BY PRIOR PARENT = CHILD
22 )
23 GROUP BY P
24 ;

C P Q
------------------------------ ------------------------------ ----------
A Z 3A E 10.8A F 16.2

這種方式仍然是10g的方法，因為使用了CONNECT_BY_ISLEAF偽列，而這個偽列是10g的新特性，由來標識當前的記錄是否是葉節點。

如果在9i中，則需要使用別的方法來實現這個功能，實現的SQL如下：

SQL> SELECT
2 SUBSTR(MAX(D), INSTR(MAX(D), '/', -1) + 1) C,
3 SUBSTR(P, 2, INSTR(P, '/', 1, 2) - 2) P,
4 POWER(10, SUM(LOG(10, Q))) Q
5 FROM
6 (
7 SELECT SYS_CONNECT_BY_PATH(CHILD, '/') || '/' P,
8 SYS_CONNECT_BY_PATH(PARENT, '/') D,
9 CHILD_QTY/PARENT_QTY Q
10 FROM T_LEVEL
11 START WITH CHILD IN
12 (
13 SELECT CHILD FROM
14 (
15 SELECT CHILD,
16 CASE WHEN LEAD(LEVELS) OVER(ORDER BY ID) > LEVELS THEN 0 ELSE 1 END LEAF
17 FROM
18 (
19 SELECT ROWNUM ID, LEVELS, PARENT, CHILD
20 FROM T_LEVEL
21 START WITH LEVELS = 1
22 CONNECT BY PRIOR CHILD = PARENT
23 ORDER SIBLINGS BY LEVELS
24 )
25 )
26 WHERE LEAF = 1
27 )
28 CONNECT BY PRIOR PARENT = CHILD
29 )
30 GROUP BY SUBSTR(P, 2, INSTR(P, '/', 1, 2) - 2);

C P Q
------------------------------ ------------------------------ ----------
A Z 3A E 10.8A F 16.2