问题描述:
传入一个产品号码参数HMAT,求生产这个产品(HMAT)所需要的材料(CHLVHMAT)及数量比例。
举例:
BP127-01
71(2426K) 90
BP127-10 10
BP127-10
127 10
24-1 30
71(2426K) 60所以,生产一个BP127-01,需要材料及数量比例为:
127 10/(90+10) X(10/(10+30+60))=0.01
24-1 10/(90+10)X(30/(10+30+60))=0.03
71(2426K) 90/(90+10) + [10/(90+10) X(60/(10+30+60))] =0.96资料简单模拟如下,当然这是最简单的两层递归..
WITH t1 AS (
SELECT 'BP127-01'HMAT FROM dual UNION ALL
SELECT 'BP127-10' FROM dual
),t2 AS (
SELECT 'BP127-01'HMAT,'71(2426K)'CHLVHMAT,90 QTY FROM dual UNION ALL
SELECT 'BP127-01'HMAT,'BP127-10'CHLVHMAT,10 QTY FROM dual UNION ALL
SELECT 'BP127-10'HMAT,'127'CHLVHMAT,10 QTY FROM dual UNION ALL
SELECT 'BP127-10'HMAT,'24-1'CHLVHMAT,30 QTY FROM dual UNION ALL
SELECT 'BP127-10'HMAT,'71(2426K)'CHLVHMAT,60 QTY FROM dual
)
SELECT t1.hmat,t2.chlvhmat,t2.qty FROM t1,t2 WHERE t1.hmat=t2.hmat;HMAT, CHLVHMAT, QTY
BP127-01 71(2426K) 90
BP127-01 BP127-10 10
BP127-10 127 10
BP127-10 24-1 30
BP127-10 71(2426K) 60
oracle开发递归
传入一个产品号码参数HMAT,求生产这个产品(HMAT)所需要的材料(CHLVHMAT)及数量比例。
举例:
BP127-01
71(2426K) 90
BP127-10 10
BP127-10
127 10
24-1 30
71(2426K) 60所以,生产一个BP127-01,需要材料及数量比例为:
127 10/(90+10) X(10/(10+30+60))=0.01
24-1 10/(90+10)X(30/(10+30+60))=0.03
71(2426K) 90/(90+10) + [10/(90+10) X(60/(10+30+60))] =0.96资料简单模拟如下,当然这是最简单的两层递归..
WITH t1 AS (
SELECT 'BP127-01'HMAT FROM dual UNION ALL
SELECT 'BP127-10' FROM dual
),t2 AS (
SELECT 'BP127-01'HMAT,'71(2426K)'CHLVHMAT,90 QTY FROM dual UNION ALL
SELECT 'BP127-01'HMAT,'BP127-10'CHLVHMAT,10 QTY FROM dual UNION ALL
SELECT 'BP127-10'HMAT,'127'CHLVHMAT,10 QTY FROM dual UNION ALL
SELECT 'BP127-10'HMAT,'24-1'CHLVHMAT,30 QTY FROM dual UNION ALL
SELECT 'BP127-10'HMAT,'71(2426K)'CHLVHMAT,60 QTY FROM dual
)
SELECT t1.hmat,t2.chlvhmat,t2.qty FROM t1,t2 WHERE t1.hmat=t2.hmat;HMAT, CHLVHMAT, QTY
BP127-01 71(2426K) 90
BP127-01 BP127-10 10
BP127-10 127 10
BP127-10 24-1 30
BP127-10 71(2426K) 60
oracle开发递归
HMAT CHLVHMAT QTY
-------------------- -------------------- ----------
BP127-01 71(2426K) 90
BP127-01 BP127-10 10
BP127-10 127 10
BP127-10 24-1 30
BP127-10 71(2426K) 60
127 24-1 50
127 71(2426K) 50
7 rows selected
SQL>
SQL> SELECT chlvhmat, SUM(rate)
2 FROM (SELECT hmat, chlvhmat, qty, LEVEL, qty / power(10, 1 + LEVEL) rate
3 FROM material
4 WHERE connect_by_isleaf = 1
5 START WITH hmat = 'BP127-01'
6 CONNECT BY PRIOR chlvhmat = hmat)
7 GROUP BY chlvhmat;
CHLVHMAT SUM(RATE)
-------------------- ----------
71(2426K) 0.965
24-1 0.035
SQL>
模拟数据正好每一层都是总和100,可能生产数据也是这样。但是如果总和不为100的话,用power函数还是会有问题。
再次感谢shiyiwan提供了另一种思路。我现在的做法是这样,就是手动计算每一层的总和。但是奇怪的是在10.1.0.2.0版本connect_by_isleaf居然显示都是0..但是在10.2.0.5.0版本又是正常的。经测试,感觉connect_by_isleaf和sum()开窗函数有问题。
WITH t1 AS (
SELECT 'BP127-01'HMAT FROM dual UNION ALL
SELECT 'BP127-10' FROM dual
),t2 AS (
SELECT 'BP127-01'HMAT,'71(2426K)'CHLVHMAT,90 QTY FROM dual UNION ALL
SELECT 'BP127-01'HMAT,'BP127-10'CHLVHMAT,10 QTY FROM dual UNION ALL
SELECT 'BP127-10'HMAT,'127'CHLVHMAT,10 QTY FROM dual UNION ALL
SELECT 'BP127-10'HMAT,'24-1'CHLVHMAT,30 QTY FROM dual UNION ALL
SELECT 'BP127-10'HMAT,'71(2426K)'CHLVHMAT,60 QTY FROM dual
)
SELECT chlvhmat,Sum(urqunty)urqunty FROM (
SELECT chlvhmat,hmat,connect_by_isleaf flag,Decode(LEVEL,'1',urqunty/Sum(urqunty) over (PARTITION BY LEVEL ORDER BY LEVEL),(connect_by_root urqunty/(Sum(urqunty) over (PARTITION BY LEVEL-1 ORDER BY LEVEL)))*urqunty/Sum(urqunty) over (PARTITION BY LEVEL ORDER BY LEVEL)) urqunty
FROM (SELECT t1.hmat,t2.chlvhmat,t2.qty urqunty FROM t1,t2 WHERE t1.hmat=t2.hmat)
START WITH hmat='BP127-01'
CONNECT BY PRIOR chlvhmat=hmat
)WHERE flag=1 GROUP BY chlvhmat
ORDER BY chlvhmat;