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

[小德]

问题描述：
传入一个产品号码参数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

[shiyiwan]

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> 

[shiyiwan]

在你的模拟记录上加了一层，变成三层递归。

[小德]

感谢shiyiwan的热心回帖。 模拟数据正好每一层都是总和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;

[小德]

引用 4 楼 shiyiwan 的回复:

生产环境的版本了解吗？ 如果是10.1.0.2.0，还得想其他办法

所幸生产环境是10.2.0.5.0版本，只能先用着了，生产环境DB版本变低可能性不大..

[shiyiwan]

生产环境的版本了解吗？ 如果是10.1.0.2.0，还得想其他办法

[一颗向往数据的心丶]

学习了、