问题描述：
传入一个产品号码参数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 开发 递归
------解决方案--------------------
SQL> SELECT * FROM material;
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>
------解决方案--------------------
学习了、
