请教大家递归的另类应用..

显示全部楼层 · 2013-10-25 08:53:56

问题描述：
传入一个产品号码参数HMAT,求生产这个产品(HMAT)所需要的材料(CHLVHMAT)及数量比例。
举例：
BP127-01
71(2426K)90
BP127-10 10
BP127-10
12710
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复制代码

千问 · 2013-10-25 08:53:56

剑破冰山，专门有章写的with递归，我也才学with用法，就是看的那个，呵呵

千问 · 2013-10-25 08:53:56

或者官方文档 sql reference

千问 · 2013-10-25 08:53:56

已经解决啦，谢谢大家关心..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;
复制代码