ORACLE 递归查询并累乘

Question

ORACLE 数据库

一个数据表

PN_CODE	RAW_PN_CODE	BASE_QTY
100001	200001	1
100001	200002	1
100002	200003	1
200001	300001	2
200001	300002	2
200002	300003	2
200002	300004	2
300001	400001	3
300004	400002	3

逻辑是：
如果只有一层，单只数量为第一层的 base_qty （1=SUM_QTY）；
如果有两层，单只数量为第一层的 base_qty 第二层的 base_qty （12 =SUM_QTY）；
如果有三层，单只用量为第一层的 base_qty 第二层的 base_qty 第三层的 base_qty （123=SUM_QTY）；
以此类推

最终结果如下表

PN_CODE	RAW_PN_CODE	BASE_QTY	SUM_QTY
100001	200001	1	1
100001	200002	1	1
100002	200003	1	1
200001	300001	2	2
200001	300002	2	2
200002	300003	2	2
200002	300004	2	2
300001	400001	3	6
300004	400002	3	6

求SQL怎么实现。

Answer 1

1、开始以为是累加，最后才发现是累乘，这个需要一个自定义函数来辅助实现：
自定义函数：

CREATE OR REPLACE FUNCTION f_mm( a_mm IN VARCHAR2 ) RETURN NUMBER IS
    l_i    INT := 0;
    l_p    INT := 1;
    l_m    INT := 0;
    l_r    NUMBER := 1;
BEGIN
    IF TRIM( a_mm ) IS NULL THEN
        RETURN 0;
    END IF;
    l_i := INSTR( a_mm, '*' );
    IF l_i < 0 THEN
        RETURN to_number( a_mm, 'long' );
    END IF;
--    dbms_output.put_line( 'l_i = ' || l_i );
    WHILE ( l_i > 0 ) LOOP
--        dbms_output.put_line( 'l_i = ' || l_i || CHR( 9 ) || 'l_p = ' || l_p || CHR( 9 ) || 'l_m = ' || l_m || CHR( 9 ) || 'l_r = ' || l_r );
        l_m := SUBSTR( a_mm, l_p, l_i - l_p );
        l_r := NVL( to_number( l_m ), 1 ) * l_r; -- 连乘号或以乘号开头则默认为 1
        l_p := l_i + 1;
        l_i := INSTR( a_mm, '*', l_i + 1 );
    END LOOP;
--    dbms_output.put_line( 'l_i = ' || l_i || CHR( 9 ) || 'l_p = ' || l_p || CHR( 9 ) || 'l_m = ' || l_m || CHR( 9 ) || 'l_r = ' || l_r );
    
    RETURN l_r * ( CASE WHEN l_p <= LENGTH( a_mm ) THEN to_number( SUBSTR( a_mm, l_p, LENGTH( a_mm ) - l_p + 1 ) ) ELSE 1 END );
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line( 'Error!' );
        RETURN -999;
END;
/

然后再执行SQL：

WITH t AS (
    SELECT '100001' as PN_CODE, '200001' as RAW_PN_CODE, 1 as BASE_QTY FROM DUAL UNION ALL
    SELECT '100001' as PN_CODE, '200002' as RAW_PN_CODE, 1 as BASE_QTY FROM DUAL UNION ALL
    SELECT '100002' as PN_CODE, '200003' as RAW_PN_CODE, 1 as BASE_QTY FROM DUAL UNION ALL
    SELECT '200001' as PN_CODE, '300001' as RAW_PN_CODE, 2 as BASE_QTY FROM DUAL UNION ALL
    SELECT '200001' as PN_CODE, '300002' as RAW_PN_CODE, 2 as BASE_QTY FROM DUAL UNION ALL
    SELECT '200002' as PN_CODE, '300003' as RAW_PN_CODE, 2 as BASE_QTY FROM DUAL UNION ALL
    SELECT '200002' as PN_CODE, '300004' as RAW_PN_CODE, 2 as BASE_QTY FROM DUAL UNION ALL
    SELECT '300001' as PN_CODE, '400001' as RAW_PN_CODE, 3 as BASE_QTY FROM DUAL UNION ALL
    SELECT '300004' as PN_CODE, '400002' as RAW_PN_CODE, 3 as BASE_QTY FROM DUAL )
SELECT connect_by_root pn_code pn_code, connect_by_root raw_pn_code raw_pn_code
     , connect_by_root base_qty AS base_qty
     , f_mm( SYS_CONNECT_BY_PATH( base_qty, '*' ) ) sum_qty
  FROM t
 WHERE connect_by_isleaf = 1
CONNECT BY PRIOR pn_code = raw_pn_code;

执行结果为：