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

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

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

作者: yangtingkun(http://yangtingkun.itpub.net)
发表于: 2008.07.13 10:18
分类: ORACLE
出处: http://yangtingkun.itpub.net/post/468/466388
---------------------------------------------------------------

[@more@]

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

简单的说，这个问题就是要实现从根节点到每个叶节点的树形记录实现连乘。

解决这个问题只有两点相对比较麻烦，一个是实现连乘，关于这个问题的实现可以参考：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

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

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