ORACLE 向上递归 然后合计用量的问题 100分

[keigojoe]

BOM表 TAB1
已知C_CODE T1,T2,T3,K2，想要获取道最顶层A_CODE的用量C_QU的总和是多少
A_ID A_CODE C_ID C_CODE C_QU
-------------------------------------------------------------------
1 A1 10 K1 1
10 K1 100 T1 4
1 A1 20 K2 2
1 A1 30 K3 2
30 K3 101 T2 1
2 A2 102 T3 10


想要获取到下面的输出结果
A_CODE C_CODE SUM(C_QU)
--------------------------------------------------------------------
A1 T2 2
A1 K2 2
A1 T1 4
A2 T3 10

其实就是向上递归，并合计某一字段的问题，请大神帮助！

[bw555]

引用 楼主 keigojoe 的回复:

BOM表 TAB1 已知C_CODE T1,T2,T3,K2，想要获取道最顶层A_CODE的用量C_QU的总和是多少 A_ID A_CODE C_ID C_CODE C_QU ------------------------------------------------------------------- 1 A1 10 K1 1 10 K1 100 T1 4 1 A1 20 K2 2 1 A1 30 K3 2 30 K3 101 T2 1 2 A2 102 T3 10 想要获取到下面的输出结果 A_CODE C_CODE SUM(C_QU) -------------------------------------------------------------------- A1 T2 2 A1 K2 2 A1 T1 4 A2 T3 10 其实就是向上递归，并合计某一字段的问题，请大神帮助！

你这数哪来的？ 就说 A1 T1这行吧 A1 K1 1 K1 T1 4 不应该是这两行的和不是5吗？这个4哪来的？

[keigojoe]

引用 1 楼 bw555 的回复:

[quote=引用 楼主 keigojoe 的回复:] BOM表 TAB1 已知C_CODE T1,T2,T3,K2，想要获取道最顶层A_CODE的用量C_QU的总和是多少 A_ID A_CODE C_ID C_CODE C_QU ------------------------------------------------------------------- 1 A1 10 K1 1 10 K1 100 T1 4 1 A1 20 K2 2 1 A1 30 K3 2 30 K3 101 T2 1 2 A2 102 T3 10 想要获取到下面的输出结果 A_CODE C_CODE SUM(C_QU) -------------------------------------------------------------------- A1 T2 2 A1 K2 2 A1 T1 4 A2 T3 10 其实就是向上递归，并合计某一字段的问题，请大神帮助！

你这数哪来的？ 就说 A1 T1这行吧 A1 K1 1 K1 T1 4 不应该是这两行的和不是5吗？这个4哪来的？[/quote] 不是求和，是乘，A1下面是K1，使用1个，K1下面是T1，使用4个，T1下面没有了，那么T1用到A1下面的一共是4个。 就是BOM累计用量的意思。 打个比方：你有一个打火机是吧，打火机下面用到一个气管，气管要用两个螺丝来固定，那么打火机下面有两个螺丝，现在问题来了，我知道要用螺丝在打火机上，但是用几个？

[bw555]

现有数据已测试通过，去实际环境中去试试吧

SQL> with t as (
  2  select 1 as A_ID,'A1' as A_CODE,10 as C_ID,'K1' as C_CODE,1 as C_QU from dual union all
  3  select 10 as A_ID,'K1' as A_CODE,100 as C_ID,'T1' as C_CODE,4 as C_QU from dual union all
  4  select 1 as A_ID,'A1' as A_CODE,20 as C_ID,'K2' as C_CODE,2 as C_QU from dual union all
  5  select 1 as A_ID,'A1' as A_CODE,30 as C_ID,'K3' as C_CODE,2 as C_QU from dual union all
  6  select 30 as A_ID,'K3' as A_CODE,101 as C_ID,'T2' as C_CODE,1 as C_QU from dual union all
  7  select 2 as A_ID,'A2' as A_CODE,102 as C_ID,'T3' as C_CODE,10 as C_QU from dual
  8  ),A AS (
  9  select ROWNUM RN,connect_by_root(A_CODE) A_CODE,C_CODE,LTRIM(sys_connect_by_path(c_qu,'*'),'*') V from t t1
 10  where connect_by_isleaf='1'
 11  connect by prior C_id=A_id
 12  start with not exists(select 1 from t where C_id=t1.A_id)
 13  )
 14  select A_CODE,C_CODE,
 15         EXP(SUM(LN(TO_NUMBER(REGEXP_SUBSTR(V, '[^/*]+', 1, LEVEL))))) STR
 16    from A
 17  CONNECT BY REGEXP_SUBSTR(V, '[^/*]+', 1, LEVEL) IS NOT NULL
 18         and rn= prior rn
 19         and prior dbms_random.value is not null
 20  GROUP BY RN,A_CODE,C_CODE
 21  ORDER BY A_CODE,C_CODE;

A_ C_        STR
-- -- ----------
A1 K2          2
A1 T1          4
A1 T2          2
A2 T3         10

[bw555]

利用A视图获取对应的累乘表达式，然后利用下面的查询对表达式进行累乘计算 注：C_QU应该是没有负数的情况吧，如果存在负数的话需要处理一下，否则负数取ln会报错的

[bw555]

可以考虑在数据库上增加一个函数，简化累乘计算的过程

SQL> CREATE OR REPLACE FUNCTION GetFormulaValue(P_Formula in varchar2) RETURN NUMBER IS
  2     V_RESULT NUMBER;
  3  BEGIN
  4    execute immediate 'SELECT '||P_Formula||' FROM DUAL' into V_RESULT;
  5    RETURN V_RESULT;
  6  END;
  7  /

函数已创建。
SQL> with t as (
  2  select 1 as A_ID,'A1' as A_CODE,10 as C_ID,'K1' as C_CODE,1 as C_QU from dual union all
  3  select 10 as A_ID,'K1' as A_CODE,100 as C_ID,'T1' as C_CODE,4 as C_QU from dual union all
  4  select 1 as A_ID,'A1' as A_CODE,20 as C_ID,'K2' as C_CODE,2 as C_QU from dual union all
  5  select 1 as A_ID,'A1' as A_CODE,30 as C_ID,'K3' as C_CODE,2 as C_QU from dual union all
  6  select 30 as A_ID,'K3' as A_CODE,101 as C_ID,'T2' as C_CODE,1 as C_QU from dual union all
  7  select 2 as A_ID,'A2' as A_CODE,102 as C_ID,'T3' as C_CODE,10 as C_QU from dual
  8  )
  9  select connect_by_root(A_CODE) A_CODE,C_CODE,GetFormulaValue(LTRIM(sys_connect_by_path(c_qu,'*'),'*')) V from t t1
 10  where connect_by_isleaf='1'
 11  connect by prior C_id=A_id
 12  start with not exists(select 1 from t where C_id=t1.A_id);

A_ C_          V
-- -- ----------
A1 T1          4
A1 K2          2
A1 T2          2
A2 T3         10

SQL>

[小灰狼W]

select connect_by_root a_code,
       c_code,
       dbms_aw.eval_number(substr(sys_connect_by_path(c_qu, '*'), 2))
  from tab1 t
 where connect_by_isleaf = 1
 start with not exists (select 1 from tab1 tx where tx.c_id = t.a_id)
connect by prior c_id = a_id

[bw555]

引用 7 楼 wildwave 的回复:

select connect_by_root a_code,
       c_code,
       dbms_aw.eval_number(substr(sys_connect_by_path(c_qu, '*'), 2))
  from tab1 t
 where connect_by_isleaf = 1
 start with not exists (select 1 from tab1 tx where tx.c_id = t.a_id)
connect by prior c_id = a_id

dbms_aw.eval_number，好东西啊 印象中有这么函数，就是想不起来是啥了，百度了下也没找到，只好自己去写了个，呵呵