递归SQL：使用递归子查询分解的聚合函数

Question

表T表示树。每个记录都是一个节点，每个节点只有一个父节点。

该查询计算每个节点的每个分支的SUM（）。

WITH t AS
        (SELECT  1 id, NULL parent_id, NULL value FROM dual UNION ALL
         SELECT 10 id,    1 parent_id, 1000 value FROM dual UNION ALL
         SELECT 20 id,    1 parent_id, 2000 value FROM dual UNION ALL
         SELECT 30 id,   10 parent_id, 3000 value FROM dual UNION ALL
         SELECT 40 id,   10 parent_id, 4000 value FROM dual UNION ALL
         SELECT 50 id,   20 parent_id, 5000 value FROM dual UNION ALL
         SELECT 60 id,    1 parent_id, 6000 value FROM dual UNION ALL
         SELECT 70 id,   60 parent_id, 7000 value FROM dual UNION ALL
         SELECT 80 id,   70 parent_id, 8000 value FROM dual
    ) SELECT CAST(LPAD(' ', (LEVEL-1)*4) || ID AS VARCHAR2(20))  id
        ,VALUE                                                   self_value
        ,(SELECT SUM (value)
          FROM   t t2
          CONNECT BY 
            PRIOR t2.ID = t2.parent_id
            START WITH t2.id = t.id)                             branch_value
      FROM   t
      CONNECT BY PRIOR t.id = t.parent_id
      START WITH t.parent_id IS NULL
      ORDER SIBLINGS BY t.id;

ID                   SELF_VALUE BRANCH_VALUE
-------------------- ---------- ------------
1                                      36000
    10                     1000         8000
        30                 3000         3000
        40                 4000         4000
    20                     2000         7000
        50                 5000         5000
    60                     6000        21000
        70                 7000        15000
            80             8000         8000

9 rows selected.

我一直在尝试使用替代的子查询分解语法来实现这个查询的相同结果。任何帮助都将不胜感激！

Answer 1

在递归查询中支持groupby之前，我认为只有一个查询是不可能的。因此，我在WITH-子句中添加了第二个子查询。也许这就足够解决你的问题了（好的脑筋急转弯）。

这将计算数据：

WITH t AS
        (SELECT  1 id, NULL parent_id, NULL value FROM dual UNION ALL
         SELECT 10 id,    1 parent_id, 1000 value FROM dual UNION ALL
         SELECT 20 id,    1 parent_id, 2000 value FROM dual UNION ALL
         SELECT 30 id,   10 parent_id, 3000 value FROM dual UNION ALL
         SELECT 40 id,   10 parent_id, 4000 value FROM dual UNION ALL
         SELECT 50 id,   20 parent_id, 5000 value FROM dual UNION ALL
         SELECT 60 id,    1 parent_id, 6000 value FROM dual UNION ALL
         SELECT 70 id,   60 parent_id, 7000 value FROM dual UNION ALL
         SELECT 80 id,   70 parent_id, 8000 value FROM dual),
hierarchy (id,ancestor,value) AS (
  SELECT t.id,t.id,t.value
    FROM t
  UNION ALL
  SELECT t.id,h.ancestor,t.value
    FROM t
      INNER JOIN hierarchy h
        ON t.parent_id = h.id)
SELECT h.ancestor, t.parent_id, t.value, SUM(h.value)
  FROM hierarchy h
    INNER JOIN t
      ON t.id = h.ancestor
  GROUP BY h.ancestor,t.value,t.parent_id;

要获得与问题中所述相同的顺序和格式，请添加路径和深度计算：

WITH t AS
        (SELECT  1 id, NULL parent_id, NULL value FROM dual UNION ALL
         SELECT 10 id,    1 parent_id, 1000 value FROM dual UNION ALL
         SELECT 20 id,    1 parent_id, 2000 value FROM dual UNION ALL
         SELECT 30 id,   10 parent_id, 3000 value FROM dual UNION ALL
         SELECT 40 id,   10 parent_id, 4000 value FROM dual UNION ALL
         SELECT 50 id,   20 parent_id, 5000 value FROM dual UNION ALL
         SELECT 60 id,    1 parent_id, 6000 value FROM dual UNION ALL
         SELECT 70 id,   60 parent_id, 7000 value FROM dual UNION ALL
         SELECT 80 id,   70 parent_id, 8000 value FROM dual),
hierarchy (id,ancestor,value,depth,path) AS (
  SELECT t.id,t.id,t.value,0,''||t.id
    FROM t
  UNION ALL
  SELECT t.id,h.ancestor,t.value,h.depth+1,h.path||'.'||t.id
    FROM t
      INNER JOIN hierarchy h
        ON t.parent_id = h.id)
SELECT LPAD(h.ancestor,p.depth*4+1,' ') AS id, t.value AS self_value, SUM(h.value) as branch_value
  FROM hierarchy h
    INNER JOIN t
      ON t.id = h.ancestor
    INNER JOIN (SELECT id,depth,path
                  FROM hierarchy
                  WHERE ancestor IN (SELECT id FROM t WHERE parent_id IS NULL)
                  ORDER BY path) p
      ON p.id = t.id
  GROUP BY h.ancestor,t.value,t.parent_id,p.path,p.depth
  ORDER BY p.path;