分解bom

我简化一下你的数据：

create table MANUF_STRUCTURE_TAB
(PART_NO VARCHAR2(25) not null,
COMPONENT_PART VARCHAR2(25) not null,
QTY_PER_ASSEMBLY NUMBER not null
);

create table INVENTORY_PART_TAB
(PART_NO VARCHAR2(25) not null,
SK_PART_CATEGORY_DB VARCHAR2(15) not null
);

INSERT INTO MANUF_STRUCTURE_TAB VALUES (‘A1’,’B’,2);
INSERT INTO MANUF_STRUCTURE_TAB VALUES (‘A2’,’B’,3);
INSERT INTO MANUF_STRUCTURE_TAB VALUES (‘B’,’C’,4);
INSERT INTO MANUF_STRUCTURE_TAB VALUES (‘C’,’D’,7);
INSERT INTO MANUF_STRUCTURE_TAB VALUES (‘A1’,’C’,6);

INSERT INTO INVENTORY_PART_TAB VALUES (‘A1’,’Product’);
INSERT INTO INVENTORY_PART_TAB VALUES (‘A2’,’Product’);
INSERT INTO INVENTORY_PART_TAB VALUES (‘B’,’SemiProdudct’);
INSERT INTO INVENTORY_PART_TAB VALUES (‘C’,’Component’);
INSERT INTO INVENTORY_PART_TAB VALUES (‘D’,’Component’);

三种方法（既然你不喜欢自定义函数就没写了）：

WITH t AS (
SELECT SYS_CONNECT_BY_PATH(a.part_no,’\’)||’\’||a.component_part||’\’ as path
,qty_per_assembly
FROM manuf_structure_tab a JOIN inventory_part_tab b
ON a.part_no = b.part_no
START WITH b.sk_part_category_db = ‘Product’
CONNECT BY a.part_no = PRIOR a.component_part
)
SELECT root_part,part,SUM(qty) AS qty
FROM (
SELECT SUBSTR(t1.path,2,INSTR(t1.path,’\’,1,2)-INSTR(t1.path,’\’,1,1)-1) as ROOT_PART
,SUBSTR(t1.path,INSTR(t1.path,’\’,-1,2)+1,INSTR(t1.path,’\’,-1,1)-INSTR(t1.path,’\’,-1,2)-1) as PART
,EXP(SUM(LN(t2.qty_per_assembly))) AS qty
FROM t t1,t t2
WHERE t1.path LIKE t2.path||’%’
GROUP BY t1.path
)
GROUP BY root_part,part;

WITH t AS (
SELECT SYS_CONNECT_BY_PATH(a.part_no,’\’)||’\’||a.component_part||’\’ as path
,qty_per_assembly
FROM manuf_structure_tab a JOIN inventory_part_tab b
ON a.part_no = b.part_no
START WITH b.sk_part_category_db = ‘Product’
CONNECT BY a.part_no = PRIOR a.component_part
)
SELECT root_part,part,SUM(qty) AS qty
FROM (
SELECT SUBSTR(t1.path,2,INSTR(t1.path,’\’,1,2)-INSTR(t1.path,’\’,1,1)-1) as ROOT_PART
,SUBSTR(t1.path,INSTR(t1.path,’\’,-1,2)+1,INSTR(t1.path,’\’,-1,1)-INSTR(t1.path,’\’,-1,2)-1) as PART
,(SELECT EXP(SUM(LN(t2.qty_per_assembly))) FROM t t2 WHERE t1.path LIKE t2.path||’%’) AS qty
FROM t t1
GROUP BY t1.path
)
GROUP BY root_part,part;

WITH t AS (
SELECT SYS_CONNECT_BY_PATH(a.part_no,’\’)||’\’||a.component_part||’\’ as path
,SYS_CONNECT_BY_PATH(a.qty_per_assembly,’\’)||’\’ as qty_path
,qty_per_assembly
FROM manuf_structure_tab a JOIN inventory_part_tab b
ON a.part_no = b.part_no
START WITH b.sk_part_category_db = ‘Product’
CONNECT BY a.part_no = PRIOR a.component_part
)
SELECT root_part,part,SUM(qty) AS qty
FROM (
SELECT SUBSTR(path,2,INSTR(path,’\’,1,2)-INSTR(path,’\’,1,1)-1) as ROOT_PART
,SUBSTR(path,INSTR(path,’\’,-1,2)+1,INSTR(path,’\’,-1,1)-INSTR(path,’\’,-1,2)-1) as PART
,(SELECT EXP(SUM(LN(SUBSTR(qty_path,INSTR(qty_path,’\’,1,rn)+1,INSTR(qty_path,’\’,1,rn+1)-INSTR(qty_path,’\’,1,rn)-1))))
FROM (SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM<=100)
WHERE SUBSTR(qty_path,INSTR(qty_path,’\’,1,rn)+1,INSTR(qty_path,’\’,1,rn+1)-INSTR(qty_path,’\’,1,rn)-1) IS NOT NULL
) AS qty
FROM t
)
GROUP BY root_part,part;

如果最后只需要原材料，你自己过滤一下。

等会有空我用10G的MODEL来写一个。

用11GR2的递归WITH子查询应该是易如反掌，但我现在没环境测试。

---

我导入楼主的数据，发现有大量重复的，这是怎么回事？父、子应该构成唯一键。如果不唯一要先DISTINCT.

经测试第三种效果最好，和10G的MODEL差不多。

[ 本帖最后由 newkid 于 2009-11-6 09:11 编辑 ]