关于connect by查询语句中查询部分的累计问题

[jimmylin040]

在计算多级BOM的时候，我们可能需要累计物料的损耗，所以要把子节点物料的损耗乘上父节点物料的损耗。
比如:
BOM level 损耗
FG 1 0.01
|-M1 2 0.02
|-M2 2 0.015
|-M2-1 3 0.01
|-M2-1.1 4 0.03


M2的损耗是 0.01×0.015,
M2-1的损耗是 0.01×0.015×0.01
M2-1.1的损耗是 0.01×0.015×0.01×0.03

假设SQL如下，如何在SQL中实现上面的损耗累计呢？

select material_code, level ,wast
  from bom
 start with parent_id = 1
connect by nocycle prior child_id = parent_id;

[minitoy]

参照http://topic.csdn.net/u/20100304/12/8fe25f7d-9a44-412f-afb7-a754016363c9.html

[tangren]

SQL> desc bom
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 MATERIAL_CODE                                      VARCHAR2(20)
 CHILD_ID                                           VARCHAR2(20)
 PARENT_ID                                          VARCHAR2(20)
 WAST                                               NUMBER

SQL> select * from bom;

MATERIAL_CODE        CHILD_ID             PARENT_ID                  WAST
-------------------- -------------------- -------------------- ----------
FG                   FG                   1                           .01
M1                   M1                   FG                          .02
M2                   M2                   FG                         .015
M2-1                 M2-1                 M2                          .01
M2-1.1               M2-1.1               M2-1                        .03

SQL> SELECT material_code,
  2         dbms_aw.eval_number(ltrim(sys_connect_by_path(wast, '*'), '*')) wast
  3    FROM bom
  4   START WITH parent_id = '1'
  5  CONNECT BY nocycle PRIOR child_id = parent_id;

MATERIAL_CODE              WAST
-------------------- ----------
FG                          .01
M1                        .0002
M2                       .00015
M2-1                   .0000015
M2-1.1               .000000045

SQL> 

[jimmylin040]

[Quote=引用 3 楼 tangren 的回复:]
SQL code
SQL> desc bom
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
MATERIAL_CODE ……
[/Quote]
哇哦~~非常感谢~

[jimmylin040]

顶，树形结构排版出不来…………希望不影响吧