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

簡單的說，這個問題就是要實現從根節點到每個葉節點的樹形記錄實現連乘。

解決這個問題只有兩點相對比較麻煩，一個是實現連乘，關於這個問題的實現可以參考：http://yangtingkun.itpub.net/post/468/466369

第二個問題就是需要構造用來GROUP BY的欄位。因為表中並不存在一個可以標識不同路徑的欄位，因此要實現聚集功能，需要人為構造一個用來聚集的欄位。

首先構造一個例子：

SQL> CREATE TABLE T_LEVEL
2 (
3 LEVELS NUMBER,
4 PARENT VARCHAR2(10),
5 CHILD VARCHAR2(10),
6 PARENT_QTY NUMBER,
7 CHILD_QTY NUMBER
8 );

表已建立。

SQL> INSERT INTO T_LEVEL VALUES (1, 'A', 'B', 1, 3);

已建立 1 行。

SQL> INSERT INTO T_LEVEL VALUES (2, 'B', 'C', 2, 3);

已建立 1 行。

SQL> INSERT INTO T_LEVEL VALUES (3, 'C', 'D', 5, 6);

已建立 1 行。

SQL> INSERT INTO T_LEVEL VALUES (4, 'D', 'E', 1, 2);

已建立 1 行。

SQL> INSERT INTO T_LEVEL VALUES (1, 'A', 'Z', 1, 3);

已建立 1 行。

SQL> COMMIT;

提交完成。

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

希望得到的結果是A包括10.8個E和3個Z。根據上面給出的連乘方法，實現連乘是很簡單的：

SQL> SELECT POWER(10, SUM(LOG(10, CHILD_QTY/PARENT_QTY))) FROM T_LEVEL;

POWER(10,SUM(LOG(10,CHILD_QTY/PARENT_QTY)))
-------------------------------------------
32.4

這樣很輕易的實現了所有記錄的連乘，不過這裡需要根據根節點到不同的葉節點的不同路徑實現連乘。現有的欄位沒有可以用來表示這種不同的路徑，因此需要構造這樣的欄位。

下面是10g中解決這個問題的SQL：

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 E 10.8A Z 3

之所以說是10g的解法，是因為裡面包含了10g的新功能，樹形查詢操作CONNECT_BY_PATH，利用這個操作可以獲取根節點的欄位。

當然，如果是9i，也可以使用其他方法來代替這個操作，只不過SQL略微麻煩一點而已：

SQL> SELECT SUBSTR(P, 2, 1) P,
2 SUBSTR(MAX(D), LENGTH(MAX(D))) C,
3 POWER(10, SUM(LOG(10, Q))) Q
4 FROM
5 (
6 SELECT SYS_CONNECT_BY_PATH(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 SUBSTR(P, 2, 1), SUBSTR(D, 2, 1);

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