【讨论】oracle递归求乘积

newkid

野花提到的对数反对数方法：

WITH T AS (
          SELECT 0 ID, NULL PID , 1 NUM FROM DUAL UNION ALL
          SELECT 1 ID, 0   PID ,2   NUM FROM DUAL UNION ALL
          SELECT 2 ID, 1   PID ,2   NUM FROM DUAL UNION ALL
          SELECT 3 ID, 2   PID ,2   NUM FROM DUAL UNION ALL
          SELECT 4 ID, 3   PID ,2   NUM FROM DUAL UNION ALL
          SELECT 22 ID, 1   PID ,3   NUM FROM DUAL UNION ALL
          SELECT 33 ID, 22   PID ,3   NUM FROM DUAL UNION ALL
          SELECT 44 ID, 33   PID ,3   NUM FROM DUAL
          )
SELECT ID,EXP(SUM(LN(NUM)))
  FROM (
SELECT CONNECT_BY_ROOT(ID) ID
      ,NUM
FROM   T
CONNECT BY ID = PRIOR PID
)
GROUP BY ID;

moseslin

lastwinner 发表于 2014-11-5 16:49
啊？我还以为你早就知道呢
很早就有人提出这方法了

模糊中是有点印象，但是在这之前肯定不会主动的想起来去用。

815517253

peter1166 发表于 2014-11-5 09:40
方法1：
WITH T AS (
          SELECT 0 ID, NULL PID , 1 NUM FROM DUAL UNION ALL

高手呀！这个工具包就知道！

theshy

WITH TMP AS (
SELECT 0 ID,NULL PID,1 NUM FROM DUAL
UNION ALL
SELECT 1 ID,0 PID,2 NUM FROM DUAL
UNION ALL
SELECT 2 ID,1 PID,2 NUM FROM DUAL
)
SELECT * FROM TMP
MODEL RETURN UPDATED ROWS
DIMENSION BY (ID)
MEASURES(PID,NUM,CAST(' ' AS VARCHAR2(100) ) AS 表达式,0 ALL_NUM)
RULES (
表达式[ID] ORDER BY ID=PRESENTNNV(表达式[CV()-1],表达式[CV()-1]||'*'||NUM[CV()],NUM[CV()])
,ALL_NUM[ID] ORDER BY ID=PRESENTNNV(ALL_NUM[CV()-1],ALL_NUM[CV()-1]*NUM[CV()],1)
);

theshy

----标量子查询大家都说很慢 不推荐
WITH TMP AS (
SELECT 0 ID,NULL PID,1 NUM FROM DUAL
UNION ALL
SELECT 1 ID,0 PID,2 NUM FROM DUAL
UNION ALL
SELECT 2 ID,1 PID,2 NUM FROM DUAL
)
SELECT T.*
,(SELECT LISTAGG(TT.NUM,'*') WITHIN GROUP (ORDER BY TT.ID)  FROM TMP TT WHERE TT.ID<=T.ID) 算数式
,DBMS_AW.EVAL_NUMBER((SELECT LISTAGG(TT.NUM,'*') WITHIN GROUP (ORDER BY TT.ID)  FROM TMP TT WHERE TT.ID<=T.ID )) RESULT
FROM TMP T
ORDER BY T.ID;
-----------------------------------------------
ID        PID        NUM        算数式        RESULT
0                1        1                   1
1        0        2        1*2                   2
2        1        2        1*2*2           4

wangqing1026

with tt AS(
      SELECT 0 ID, NULL PID , 1 NUM FROM DUAL UNION ALL
          SELECT 1 ID, 0   PID ,2   NUM FROM DUAL UNION ALL
          SELECT 2 ID, 1   PID ,2   NUM FROM DUAL UNION ALL
          SELECT 3 ID, 2   PID ,2   NUM FROM DUAL UNION ALL
          SELECT 4 ID, 3   PID ,2   NUM FROM DUAL UNION ALL
          SELECT 22 ID, 1   PID ,3   NUM FROM DUAL UNION ALL
          SELECT 33 ID, 22   PID ,3   NUM FROM DUAL UNION ALL
          SELECT 44 ID, 33   PID ,3   NUM FROM DUAL )
   SELECT a.id,a.pid,a.num,DBMS_AW.eval_number(substr(sys_connect_by_path(a.num,'*'),2,10)）num_all
   FROM TT A
   start with a.pid is null
   connect by prior a.id=a.pid
   ;