使用connect by子句进行递归插入 [英] Recursive Insert using connect by clause
问题描述
我以下列方式在表中具有层次结构数据(右),这创建了层次结构,如左图所示.表保存在oracle 11g中.
I have hierarchical data (right) in table in following manner which creates Hierarchy as shown in left. Tables are kept in oracle 11g.
TREE Hierarchy Tree Table
-------------- Element Parent
------ ------
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
我的数据表的值如下.它包含所有叶节点的值.
数据表
My data table has values as follows. It contains values for all leaf nodes.
Data Table
Element Value
C111 3
C112 3
C121 3
C122 3
C211 3
C212 3
C221 3
C222 3
P11 6
我需要生成插入语句, 最好是单个插入语句 ,它将根据子项的值之和在数据表中插入行. 请注意,我们仅需要为数据表中未包含值的那些父母计算总和.
I need to generate insert statement, preferably single insert statement which will insert rows in data table based on sum of values of the children. Please note we need to calculate sum for only those parents whose value is not present in data table.
数据表(插入后预期)
Element Value
C111 3
C112 3
C121 3
C122 3
C211 3
C212 3
C221 3
C222 3
P11 6
-- Rows to insert
P12 6
P21 6
P22 6
P1 12
P2 12
P0 24
推荐答案
如果所有叶节点的高度都相同(此处为lvl = 4),则可以使用ROLLUP编写一个简单的CONNECT BY查询:
If all leaf nodes are at the same height (here lvl=4), you can write a simple CONNECT BY query with a ROLLUP:
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需要确切知道要考虑的列数,因此上述方法无效.
If the height of the leaf nodes is unknown/unbounded this gets more hairy. The above approach wouldn't work since ROLLUP needs to know exactly how many columns are to be considered.
在这种情况下,您可以在自连接中使用树结构:
In that case, you could use the tree structure in a self-join :
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
这篇关于使用connect by子句进行递归插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!