ツリー関数による部品数の計算

サンプルデータは次のとおりです.

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 I
5 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 I
5 rows selected