oracle展bom逻辑,oracle 求BOM树型展开的总用量

最新推荐文章于 2022-05-12 10:41:35 发布

贤睿

最新推荐文章于 2022-05-12 10:41:35 发布

阅读量375

点赞数

文章标签： oracle展bom逻辑

一个BOM展开求总使用量问题求助:

当然也请各路高手拔刀相助

1.建表:

表1 结构表

create table

MANUF_STRUCTURE_TAB

(PART_NO VARCHAR2(25) not null,

--父

COMPONENT_PART

VARCHAR2(25) not null, --子

QTY_PER_ASSEMBLY

NUMBER not null)

--子对父的构成数(即1个父由多少个子构成)

表2 分类表

create table

INVENTORY_PART_TAB

(PART_NO VARCHAR2(25) not null, --编码

SK_PART_CATEGORY_DB VARCHAR2(15) not null) --类别

2.说明:

表2中SK_PART_CATEGORY_DB分3类:

Product:成品

SemiProdudct:半成品

Component:原材料

3.想要结果:

1个成品所需要原材料的总使用量

PART_NO COMPONENT_PART T_QTY

......

测试数据及想要结果(想要结果未能列完)

想用sys_connect_by_path

把QTY_PER_ASSEMBLY用乘號串起來,

然後調用一個能執行動態SQL的function以得到結果.

简化一下你的数据：

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

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

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

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

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

)

GROUP BY

root_part,part;

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

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

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

---------------

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

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

上面的方法都不新鲜了，这个10G的MODEL方法好像没见过：

WITH t AS

(

SELECT

SYS_CONNECT_BY_PATH(a.part_no,'\')||'\'||a.component_part as

path

,qty_per_assembly

,LEVEL

lvl

FROM

manuf_structure_tab a JOIN inventory_part_tab b

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,1)+1) as PART

,qty

FROM

MODEL IGNORE NAV RETURN

UPDATED ROWS

DIMENSION BY (path)

MEASURES (qty_per_assembly, 1

qty,lvl)

RULES AUTOMATIC ORDER

(

qty[any] order by path=(CASE

WHEN lvl[cv()]=1 THEN

qty_per_assembly[cv()]

ELSE qty_per_assembly[cv()]

qty[SUBSTR(cv(),1,INSTR(cv(),'\',-1,1)-1)]

END)

)

GROUP BY

root_part,part;

我的manuf_structure_tab有15万记录,INVENORY_PART_TAB有8W.

目前执行了5MIN还没反应.

WITH t AS

(

SELECT

SYS_CONNECT_BY_PATH(a.part_no,'\')||'\'||a.component_part||'\' as

path

,qty_per_assembly

FROM

manuf_structure_tab a,inventory_part_tab

where

a.part_no = b.part_no

and

a.contract=b.contract

and

b.contract='SKY'

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

GROUP BY

t1.path

)

GROUP BY

root_part,part

这样呢？

WITH t AS

(

SELECT

SYS_CONNECT_BY_PATH(a.part_no,'\')||'\' as path

,component_part as

part

,LEVEL

lvl

,SYS_CONNECT_BY_PATH(a.qty_per_assembly,'\')||'\' as

qty_path

FROM

manuf_structure_tab a JOIN inventory_part_tab b

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)-2) as

ROOT_PART

,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 rn<=lvl

) AS

qty

FROM

)

GROUP BY

root_part,part;

---------------------------------------------------------------------------------------------------------------

| Id |

(%CPU)| Time |

---------------------------------------------------------------------------------------------------------------

| 0 |

SELECT STATEMENT |

| 804 |

3163K| | 1216 (1)|

00:00:15 |

| 1 |

SORT AGGREGATE | | 1 | 13 |

|* 2 |

VIEW | |

1 | 13 | | 2

(0)| 00:00:01 |

| 3 |

COUNT | |

| 4 |

CONNECT BY WITHOUT FILTERING|

| | | | | |

| 5 |

FAST DUAL

| 1 |

2 (0)|

00:00:01 |

| 6 |

HASH GROUP BY | |

804 | 3163K|

13M| 1216

(1)| 00:00:15 |

| 7 |

VIEW | |

2587 | 9M|

9 (12)|

00:00:01 |

|* 8 |

CONNECT BY WITH FILTERING

| |

|* 9 |

| 10 |

COUNT

| |

|* 11 | HASH JOIN | | 2587 |

126K| | 9

(12)| 00:00:01 |

| 12 |

TABLE ACCESS FULL |

INVENTORY_PART_TAB | 887 |

20401 | |

3 (0)|

00:00:01 |

| 13 |

TABLE ACCESS FULL |

MANUF_STRUCTURE_TAB | 2587 | 69849 |

5 (0)|

00:00:01 |

|* 14 | HASH JOIN | | | | | | |

| 15 |

CONNECT BY

PUMP |

| | | | | |

| 16 |

COUNT

| |

|* 17 | HASH JOIN | | 2587 |

126K| | 9

(12)| 00:00:01 |

| 18 |

TABLE ACCESS FULL |

INVENTORY_PART_TAB | 887 |

20401 | |

3 (0)|

00:00:01 |

| 19 |

TABLE ACCESS FULL |

MANUF_STRUCTURE_TAB | 2587 | 69849 |

5 (0)|

00:00:01 |

| 20 |

|* 21 | HASH JOIN | |

2587 | 126K|

9 (12)|

00:00:01 |

| 22 |

TABLE

ACCESS FULL | INVENTORY_PART_TAB

| 887 | 20401 |

3 (0)|

00:00:01 |

| 23 |

TABLE

ACCESS FULL | MANUF_STRUCTURE_TAB |

2587 | 69849 | | 5 (0)| 00:00:01

---------------------------------------------------------------------------------------------------------------

从你的数据量来看，遍历深度应该很浅啊？如果单独挑几种产品(在START

WITH中指定)，速度怎么样？结果对吗？

单独把WITH里面的查询CREATE TABLE

AS...要花多少时间？生成的结果多大？这一步完成后尽量过滤(比如只保留你要的原料)，让后面的乘法计算更少。

SELECT xmlquery('1*2*3*4'

returning content).getnumberval() FROM DUAL;

但是10G不支持变量，只能常量：

SELECT xmlquery(str returning

content).getnumberval()

FROM (SELECT '1*2*3*4' as str

FROM DUAL);

ERROR at line

ORA-19102: XQuery string

literal expected

----

11GR2:

WITH t(root_part,part,qty) AS

(

SELECT a.part_no as

root_part, component_part as part,qty_per_assembly as

qty

FROM

manuf_structure_tab a JOIN inventory_part_tab b

a.part_no = b.part_no

WHERE b.sk_part_category_db =

'Product'

UNION ALL

SELECT

a.root_part,b.component_part,a.qty*b.qty_per_assembly

FROM t a,

manuf_structure_tab b

WHERE a.part =

b.part_no

)

SELECT

root_part,part,SUM(qty) AS qty

FROM

GROUP BY

root_part,part;

ROOT_PART PART QTY

-------------------------

------------------------- ----------

A2 C 12

A1 D 98

A1 B 2

A2 B 3

A1 C 14

A2 D 84

贤睿

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
oracle展bom逻辑,oracle 求BOM树型展开的总用量

一个BOM展开求总使用量问题求助:当然也请各路高手拔刀相助1.建表:表1 结构表create tableMANUF_STRUCTURE_TAB(PART_NO VARCHAR2(25) not null,--父COMPONENT_PARTVARCHAR2(25) not null, --子QTY_PER_ASSEMBLYNUMBER not null)--子对父的构成数(即1个父由多少个子构成...
复制链接

扫一扫