sql - 使用connect by子句递归插入

Question

我在表中有分层数据(右)，以下列方式创建层次结构，如左图所示。表保存在 oracle 11g 中。

TREE 层次结构树表
-------------- 元素父级
------ ------
P0 P0
P1 P1 P0
P11 P2 P0
C111 P11 P1
C112 P12 P1
P12 P21 P2
C121 P22 P2
C122 C111 P11
P2 C112 P11
P21 C121 P12
C211 C122 P12
C212 C211 P21
P22 C212 P21
C221 C221 P22
C222 C222 P22

我的数据表的值如下。它包含所有叶节点的值。
数据表

元素值
C111 3
C112 3
C121 3
C122 3
C211 3
C212 3
C221 3
C222 3
P11 6

我需要生成插入语句，最好是单个插入语句 这将根据子项的值总和在数据表中插入行。
请注意，我们只需要为数据表中不存在值的 parent 计算总和。

数据表(插入后预期)

元素值
C111 3
C112 3
C121 3
C122 3
C211 3
C212 3
C221 3
C222 3
P11 6

-- 要插入的行
P12 6
P21 6
P22 6
P1 12
P2 12
P0 24

Answer 1

如果所有叶节点都在相同的高度(这里 lvl=4)，您可以使用 ROLLUP 编写一个简单的 CONNECT BY 查询:

SQL> SELECT lvl0,
  2         regexp_substr(path, '[^/]+', 1, 2) lvl1,
  3         regexp_substr(path, '[^/]+', 1, 3) lvl2,
  4         SUM(VALUE) sum_value
  5    FROM (SELECT sys_connect_by_path(t.element, '/') path,
  6                 connect_by_root(t.element) lvl0,
  7                 t.element, d.VALUE, LEVEL lvl
  8             FROM tree t
  9             LEFT JOIN DATA d ON d.element = t.element
 10            START WITH t.PARENT IS NULL
 11           CONNECT BY t.PARENT = PRIOR t.element)
 12   WHERE VALUE IS NOT NULL
 13     AND lvl = 4
 14   GROUP BY lvl0, ROLLUP(regexp_substr(path, '[^/]+', 1, 2),
 15                         regexp_substr(path, '[^/]+', 1, 3));

LVL0 LVL1  LVL2   SUM_VALUE
---- ----- ----- ----------
P0   P1    P11            6
P0   P1    P12            6
P0   P1                  12
P0   P2    P21            6
P0   P2    P22            6
P0   P2                  12
P0                       24

插入看起来像:

INSERT INTO data (element, value) 
(SELECT coalesce(lvl2, lvl1, lvl0), sum_value
   FROM <query> d_out
  WHERE NOT EXISTS (SELECT NULL
                      FROM data d_in
                     WHERE d_in.element = coalesce(lvl2, lvl1, lvl0)));

如果叶节点的高度未知/无界，这会变得更加棘手。上述方法不起作用，因为 ROLLUP 需要确切知道要考虑多少列。

在这种情况下，您可以在自联接中使用树结构:

SQL> WITH HIERARCHY AS (
  2     SELECT t.element, path, VALUE
  3       FROM (SELECT sys_connect_by_path(t.element, '/') path,
  4                    connect_by_isleaf is_leaf, ELEMENT
  5                FROM tree t
  6               START WITH t.PARENT IS NULL
  7              CONNECT BY t.PARENT = PRIOR t.element) t
  8       LEFT JOIN DATA d ON d.element = t.element
  9                       AND t.is_leaf = 1
 10  )
 11  SELECT h.element, SUM(elements.value)
 12    FROM HIERARCHY h
 13    JOIN HIERARCHY elements ON elements.path LIKE h.path||'/%'
 14   WHERE h.VALUE IS NULL
 15   GROUP BY h.element
 16   ORDER BY 1;

ELEMENT SUM(ELEMENTS.VALUE)
------- -------------------
P0                       24
P1                       12
P11                       6
P12                       6
P2                       12
P21                       6
P22                       6