BOM 成品_半成品_原材料_需求_sql方案【转载】

admin PL/SQL 2017-08-18 243 次浏览 没有评论

转载newkid: http://www.itpub.net/thread-1225621-2-1.html

表结构说明:

表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)   --类别

#######需求:

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

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');
commit;

SCOTT >SELECT * from MANUF_STRUCTURE_TAB;

PART_NO             COMPONENT_PART        QTY_PER_ASSEMBLY
------------------- --------------------- ----------------
A1                  B                                    2
A2                  B                                    3
B                   C                                    4
C                   D                                    7
A1                  C                                    6

Elapsed: 00:00:00.00
SCOTT >
SCOTT >select * from INVENTORY_PART_TAB;

PART_NO            SK_PART_CATEGORY_DB
------------------ ------------------------------
A1                 Product
A2                 Product
B                  SemiProdudct
C                  Component
D                  Component

Elapsed: 00:00:0
SCOTT >

使用递归with实现(11gr2及以后)(http://www.itpub.net/thread-1225621-2-1.html),

SCOTT >WITH t(root_part,part,qty) AS (
  2  SELECT a.part_no as root_part, component_part as part,qty_per_assembly as qty
  3    FROM manuf_structure_tab a JOIN inventory_part_tab b
  4         ON a.part_no = b.part_no
  5  WHERE b.sk_part_category_db = 'Product'
  6  UNION ALL
  7  SELECT a.root_part,b.component_part,a.qty*b.qty_per_assembly 
  8    FROM t a, manuf_structure_tab b
  9  WHERE a.part = b.part_no
 10  )
 11  SELECT root_part,part,SUM(qty) AS qty
 12    FROM t
 13  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
6 rows selected.
Elapsed: 00:00:00.00
SCOTT >

普通方法实现:

三种方法:

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;

发表评论

电子邮件地址不会被公开。 必填项已用*标注

回顶部