一个树形聚集SQL问题（二） (物料BOM消耗量计算) zt

一个树形聚集SQL问题（二） (物料BOM消耗量计算)

===========================================================

作者: yangtingkun(http://yangtingkun.itpub.net)
发表于: 2008.07.15 23:27
分类: ORACLE
出处: http://yangtingkun.itpub.net/post/468/466633
---------------------------------------------------------------

[@more@]

看到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_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

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

转载于:http://blog.itpub.net/82387/viewspace-1009652/