再发一个递归应用，坐等大神

小德 2013-10-30 03:05:04
问题描述：
传入一个产品号码参数HMAT,求生产这个产品(HMAT)所需要的材料(CHLVHMAT)及数量比例。
举例：
BP127-01
71(2426K) 90
BP127-10 10
BP127-10
127 10
24-1 30
71(2426K) 60
127
24-1 30
71(2426K) 30
所以，生产一个BP127-01，需要材料及数量比例为：
24-1=10/(90+10)*(30/(10+30+60))+(10/(90+10))*(10/(10+30+60))*(30/(30+30))=0.035
71(2426K)=90/(90+10) + 10/(90+10)*(60/(10+30+60))+(10/(90+10))*(10/(10+30+60))*(30/(30+30))=0.965
资料简单模拟如下，当然这是最简单的两层递归..
WITH t1 AS (

SELECT 'BP127-01'HMAT FROM dual UNION ALL 

SELECT 'BP127-10' FROM dual UNION ALL 

SELECT '127' 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 UNION ALL

SELECT '127'HMAT,'24-1'CHLVHMAT,30 QTY FROM dual UNION ALL

SELECT '127'HMAT,'71(2426K)'CHLVHMAT,30 QTY FROM dual

)

SELECT LEVEL,chlvhmat,hmat,connect_by_isleaf flag,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 

    ORDER siblings BY hmat;



LEVEL, CHLVHMAT, HMAT, FLAG, URQUNTY

1 71(2426K) BP127-01 1 90

1 BP127-10  BP127-01 0 10

2 127       BP127-10 0 10

3 24-1      127      1 30

3 71(2426K) 127      1 30

2 24-1      BP127-10 1 30

2 71(2426K) BP127-10 1 60
...全文
312 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
shiyiwan 2013-10-31
  • 打赏
  • 举报
 回复
不是大神　不必谢　:)
小德 2013-10-31
  • 打赏
  • 举报
 回复
引用 7 楼 shiyiwan 的回复:
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
Connected as RCOTRPR1
 
SQL> 
SQL> CREATE OR REPLACE FUNCTION cal_rate(v_level_rate IN VARCHAR2) RETURN NUMBER AS
  2    v_rate NUMBER;
  3  BEGIN
  4    EXECUTE IMMEDIATE 'select ' || v_level_rate || ' from dual'
  5      INTO v_rate;
  6    RETURN v_rate;
  7  END cal_rate;
  8  /
 
Function created
 
SQL> 
SQL> WITH t1 AS (
  2  SELECT 'BP127-01'HMAT FROM dual UNION ALL
  3  SELECT 'BP127-10' FROM dual UNION ALL
  4  SELECT '127' FROM dual
  5  ),t2 AS (
  6  SELECT 'BP127-01'HMAT,'71(2426K)'CHLVHMAT,80 QTY FROM dual UNION ALL
  7  SELECT 'BP127-01'HMAT,'BP127-10'CHLVHMAT,20 QTY FROM dual UNION ALL
  8  SELECT 'BP127-10'HMAT,'127'CHLVHMAT,10 QTY FROM dual UNION ALL
  9  SELECT 'BP127-10'HMAT,'24-1'CHLVHMAT,30 QTY FROM dual UNION ALL
 10  SELECT 'BP127-10'HMAT,'71(2426K)'CHLVHMAT,60 QTY FROM dual UNION ALL
 11  SELECT '127'HMAT,'24-1'CHLVHMAT,30 QTY FROM dual UNION ALL
 12  SELECT '127'HMAT,'71(2426K)'CHLVHMAT,30 QTY FROM dual
 13  )
 14  SELECT  chlvhmat, sum(cal_rate(substr(sys_connect_by_path(rate, '*'), 2))) rate FROM (
 15  SELECT LEVEL lv, chlvhmat, hmat,
 16                 urqunty / SUM(urqunty) over(PARTITION BY LEVEL ORDER BY LEVEL) rate
 17             FROM (SELECT t1.hmat, t2.chlvhmat, t2.qty urqunty FROM t1, t2 WHERE t1.hmat = t2.hmat)
 18            START WITH hmat = 'BP127-01'
 19           CONNECT BY PRIOR chlvhmat = hmat) WHERE connect_by_isleaf = 1
 20    start with hmat='BP127-01'
 21  connect by prior chlvhmat = hmat GROUP BY chlvhmat;
 
CHLVHMAT        RATE
--------- ----------
71(2426K)       0.93
24-1            0.07
 
SQL>
比较取巧，因为sys_connect_by_path可以连接所有的父节点，用乘号连接变成varchar2类型，再用动态sql把乘法结果算出来，需要创建一个function.
很有意思的想法，特别是用function计算，很有亮点啊。 最后更正了一点，应该用SUM(urqunty) over(PARTITION BY hmat ORDER BY LEVEL)计算每棵树的总量才对。 问题已解决，感谢大神。
shiyiwan 2013-10-30
  • 打赏
  • 举报
 回复 
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
Connected as RCOTRPR1
 
SQL> 
SQL> CREATE OR REPLACE FUNCTION cal_rate(v_level_rate IN VARCHAR2) RETURN NUMBER AS
  2    v_rate NUMBER;
  3  BEGIN
  4    EXECUTE IMMEDIATE 'select ' || v_level_rate || ' from dual'
  5      INTO v_rate;
  6    RETURN v_rate;
  7  END cal_rate;
  8  /
 
Function created
 
SQL> 
SQL> WITH t1 AS (
  2  SELECT 'BP127-01'HMAT FROM dual UNION ALL
  3  SELECT 'BP127-10' FROM dual UNION ALL
  4  SELECT '127' FROM dual
  5  ),t2 AS (
  6  SELECT 'BP127-01'HMAT,'71(2426K)'CHLVHMAT,80 QTY FROM dual UNION ALL
  7  SELECT 'BP127-01'HMAT,'BP127-10'CHLVHMAT,20 QTY FROM dual UNION ALL
  8  SELECT 'BP127-10'HMAT,'127'CHLVHMAT,10 QTY FROM dual UNION ALL
  9  SELECT 'BP127-10'HMAT,'24-1'CHLVHMAT,30 QTY FROM dual UNION ALL
 10  SELECT 'BP127-10'HMAT,'71(2426K)'CHLVHMAT,60 QTY FROM dual UNION ALL
 11  SELECT '127'HMAT,'24-1'CHLVHMAT,30 QTY FROM dual UNION ALL
 12  SELECT '127'HMAT,'71(2426K)'CHLVHMAT,30 QTY FROM dual
 13  )
 14  SELECT  chlvhmat, sum(cal_rate(substr(sys_connect_by_path(rate, '*'), 2))) rate FROM (
 15  SELECT LEVEL lv, chlvhmat, hmat,
 16                 urqunty / SUM(urqunty) over(PARTITION BY LEVEL ORDER BY LEVEL) rate
 17             FROM (SELECT t1.hmat, t2.chlvhmat, t2.qty urqunty FROM t1, t2 WHERE t1.hmat = t2.hmat)
 18            START WITH hmat = 'BP127-01'
 19           CONNECT BY PRIOR chlvhmat = hmat) WHERE connect_by_isleaf = 1
 20    start with hmat='BP127-01'
 21  connect by prior chlvhmat = hmat GROUP BY chlvhmat;
 
CHLVHMAT        RATE
--------- ----------
71(2426K)       0.93
24-1            0.07
 
SQL>
比较取巧，因为sys_connect_by_path可以连接所有的父节点，用乘号连接变成varchar2类型，再用动态sql把乘法结果算出来，需要创建一个function.
shiyiwan 2013-10-30
  • 打赏
  • 举报
 回复
恩，确实没考虑周全。
小德 2013-10-30
  • 打赏
  • 举报
 回复
引用 4 楼 shiyiwan 的回复:
WITH t1 AS
 (SELECT 'BP127-01' hmat
    FROM dual
  UNION ALL
  SELECT 'BP127-10'
    FROM dual
  UNION ALL
  SELECT '127' 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
  UNION ALL
  SELECT '127' hmat, '24-1' chlvhmat, 30 qty
    FROM dual
  UNION ALL
  SELECT '127' hmat, '71(2426K)' chlvhmat, 30 qty FROM dual)
SELECT chlvhmat, SUM(urqunty) urqunty FROM (
SELECT LEVEL lv, chlvhmat, hmat, connect_by_isleaf flag,
               urqunty / SUM(urqunty) over(PARTITION BY LEVEL ORDER BY LEVEL) / power(10, LEVEL - 1) 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;
大神，你还是习惯用power哈，可能我这个例子数据刚好误导你，你看，假如树是这样： BP127-01 71(2426K) 80 BP127-10 20 BP127-10 127 10 24-1 30 71(2426K) 60 127 24-1 30 71(2426K) 30 所以，生产一个BP127-01，需要材料及数量比例为： 24-1=20/(80+20)*(30/(10+30+60))+(20/(90+10))*(10/(10+30+60))*(30/(30+30))=0.07 71(2426K)=80/(80+20) + 20/(80+20)*(60/(10+30+60))+(20/(80+20))*(10/(10+30+60))*(30/(30+30))=0.93 但是你的结果出来确实不同的。
shiyiwan 2013-10-30
  • 打赏
  • 举报
 回复 
WITH t1 AS
 (SELECT 'BP127-01' hmat
    FROM dual
  UNION ALL
  SELECT 'BP127-10'
    FROM dual
  UNION ALL
  SELECT '127' 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
  UNION ALL
  SELECT '127' hmat, '24-1' chlvhmat, 30 qty
    FROM dual
  UNION ALL
  SELECT '127' hmat, '71(2426K)' chlvhmat, 30 qty FROM dual)
SELECT chlvhmat, SUM(urqunty) urqunty FROM (
SELECT LEVEL lv, chlvhmat, hmat, connect_by_isleaf flag,
               urqunty / SUM(urqunty) over(PARTITION BY LEVEL ORDER BY LEVEL) / power(10, LEVEL - 1) 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;
shiyiwan 2013-10-30
  • 打赏
  • 举报
 回复
阿，CSDN没有提醒，我看下。
小德 2013-10-30
  • 打赏
  • 举报
 回复
引用 1 楼 shiyiwan 的回复:
和上次的区别是什么？除了总和不为100，其他好像是一样的。
大神，看我给你留言没，我之前的方法用connect_by_root urqunty取父亲节点在上层树的数量，但是实际上取的是顶层父亲节点的数量，这是不对的。现在纠结这么取上层的数量。
shiyiwan 2013-10-30
  • 打赏
  • 举报
 回复
和上次的区别是什么？除了总和不为100，其他好像是一样的。
递归入门、递归遍历、递归穷举算法
本课程带你从递归算法基础入手，课程是精讲大量实际项目中常用到的案例，课程深入浅出，包括递归入门，递归遍历、弟归穷举算法及各种项目，适合算法爱好者一起学习，后附每堂课项目源码，感兴趣可以观注博客，不定期...
怎么写一个递归程序
如何写一个递归程序　总是听到大大们说递归递归的，自己写程序的时候却用不到递归。其中的原因，一个是害怕写递归，另一个就是不知道什么时候用递归。这篇文章就浅析一下，希望看完之后不再害怕递归，这就是本文最大...
数据结构笔记——栈的应用（递归）
函数调用时，需要用一个栈存储： ①调用返回地址 ②实参 ③局部变量 二、栈在递归中的应用 适合用“递归”算法解决：可以把原始问题转换为属性相同，但规模较小的问题 Eg 1: 计算正整数的阶乘 递归调用...
写一个递归函数:输入一个非负整数，返回组成它的数字之和
写一个递归函数DigitSum(n)，输入一个非负整数，返回组成它的数字之和，例如，调用DigitSum(1729)，则应该返回1 + 7 + 2 + 9，它的和是19。 分析：要得到输入数的每一位之和，除了用普通的方法把每一位取出来再加在...
如何写一个递归程序
其中的原因，一个是害怕写递归，另一个就是不知道什么时候用递归。这篇文章就浅析一下，希望看完之后不再害怕递归，这就是本文最大的目的。 递归到底有什么意义？ 在说怎么写递归之前必须要说一下它的意义，其实这...
基础和管理

17,377

社区成员

95,128

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 基础和管理
社区管理员
  • 基础和管理社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章