方法1:
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
)
SELECT ID , PID , LTRIM(SYS_CONNECT_BY_PATH(NUM,'*'),'*') || ' = ' STR
,DBMS_AW.eval_number(LTRIM(SYS_CONNECT_BY_PATH(NUM,'*'),'*')) VAL
FROM T
CONNECT BY PRIOR ID = PID
START WITH PID IS NULL ;
ID PID STR VAL
---------- ---------- -------------------------------------------------------------------------------- ----------
0 1 = 1
1 0 1*2 = 2
2 1 1*2*2 = 4
方法2:
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
)
,T1(ID,PID,NUM) AS (
SELECT ID,PID,NUM FROM T WHERE PID IS NULL
UNION ALL
SELECT T.ID , T.PID , T.NUM*T1.NUM
FROM T1 ,T
WHERE T1.ID = T.PID
)
SELECT * FROM T1 ;
ID PID NUM
---------- ---------- ----------
0 1
1 0 2
2 1 4