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

Question

ORACLE向上递归 然后合计用量ORACLE向上递归合计用量递归合计

Answer 1

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

Answer 2

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

Answer 3

现有数据已测试通过，去实际环境中去试试吧
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

Answer 4

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

Answer 5

可以考虑在数据库上增加一个函数，简化累乘计算的过程
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         10SQL>

Answer 6

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

Answer 7

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