用树形函数计算零件数

来源：互联网发布：开淘宝店要多少钱押金编辑：程序博客网时间：2024/05/14 20:27

样例数据如下：

CREATE OR REPLACE VIEW t AS (SELECT 'B' a, 'A' b, 2 n FROM dual UNION ALL  SELECT 'C' a, 'A' b, 4 n FROM dual UNION ALL  SELECT 'D' a, 'B' b, 3 n FROM dual UNION ALL  SELECT 'E' a, 'B' b, 3 n FROM dual UNION ALL  SELECT 'F' a, 'B' b, 2 n FROM dual UNION ALL  SELECT 'G' a, 'C' b, 4 n FROM dual UNION ALL  SELECT 'H' a, 'C' b, 2 n FROM dual UNION ALL  SELECT 'I' a, 'H' b, 5 n FROM dual);

要求

做 1个A ，需要 2个 B， 4个 C 做 1个B ，需要 3个 D， 3个 E， 2个 F做 1个C ，需要 4个 G， 2个 H做 1个H ，需要 5个 I  问  ：做 5个A一共需要多少个材料 ？

可以直接通过树形函数取得叶子节点信息

SQL> WITH x1 AS  2   (SELECT a, b, n, ltrim(sys_connect_by_path(to_char(n), '*'), '*') AS cnt  3      FROM t  4     WHERE connect_by_isleaf = 1  5     START WITH b = 'A'  6    CONNECT BY (PRIOR a) = b)  7  SELECT dbms_aw.eval_number(cnt) AS cnt, a FROM x1;       CNT A---------- -         6 D         6 E         4 F        16 G        40 I5 rows selected

这儿用了个小技巧，用dbms_aw.eval_number函数处理了树形函数的返回值，也可以用with语句，这时就不需要树形函数了

SQL> WITH x0(a,b,n) AS  2   (SELECT a, b, n FROM t WHERE b = 'A'  3    UNION ALL  4    SELECT t.a, t.b, t.n * x0.n AS n FROM x0 INNER JOIN t ON t.b = x0.a)  5  SELECT x0.n,x0.a FROM x0 LEFT JOIN t ON t.b = x0.a WHERE t.b IS NULL  6   ORDER BY 2;         N A---------- -         6 D         6 E         4 F        16 G        40 I5 rows selected

0 0