从Oracle表构建树
从Oracle表构建树
提问于 2016-04-04 11:05:45
我在Oracle中有两个表来表示各种树，这些表是："PARTS_TREE_ENTRIES“，其中存储了所有节点(包括父节点和子节点)，而"PARTS_ITEMS”则描述了节点之间的关系。
在表TREE_ITEMS中，列COMPONENT_ID表示父级，COMPONENT_ITEMID表示其子级。
有不止一棵树，所有树的节点都在同一个表"TREE_ENTRIES“中，这样就可以更容易地理解--这是几棵树的表示：
这些是他们在表格中的条目：
正如您在表TREE_ITEMS中看到的那样，作为分支根的节点具有COMPONENT_ID的值"A“
我需要帮助构建一个查询，以获得具有父级和ID的最后一个级别的所有节点的列表，输出应该类似于以下内容：
我读过关于“连接通过”的条款，但我从未使用过，我也不知道从哪里开始。
非常感谢您提前！
回答 1
Stack Overflow用户
回答已采纳
发布于 2016-04-04 14:07:43
我意识到您显式地询问了connect by查询，但我认为我们可以在SQL中使用递归，因为它通过添加一些功能来完成相同的工作。
根据你的样本结果，我最好的猜测是这样的。
我不清楚您想要连接的数据还是分开的列，所以这两种情况都有一个建议。
with tree_view(tree_id, component_id, component_itemid, id, part_tree_id, componentid, name, lvl, tree_path, root_id) as (
select t.tree_id, t.component_id, t.component_itemid, t.id, e.part_tree_id, e.componentid, e.name, 1, e.name, component_itemid
from tree_items t,
tree_entries e
where t.component_itemid = e.componentid
and t.tree_id = e.part_tree_id
and t.component_id = 'A'
union all
select t.tree_id, t.component_id, t.component_itemid, t.id, e.part_tree_id, e.componentid, e.name, tv.lvl + 1, tv.tree_path || '=>' || e.name, tv.root_id
from tree_items t,
tree_entries e,
tree_view tv
where to_char(tv.component_itemid) = to_char(t.component_id)
and to_char(e.componentid) = to_char(t.component_itemid)
and tv.tree_id = t.tree_id
) -- end of hierarchy view
search depth first by lvl set order1
select tree_path,
name,
componentid,
regexp_substr(tree_path, '[[:alpha:]]+', 1, 1) lvl1_part,
regexp_substr(tree_path, '[[:alpha:]]+', 1, 2) lvl2_part,
regexp_substr(tree_path, '[[:alpha:]]+', 1, 3) lvl3_part -- add more if there are further levels down the tree
from (
select tree_id, component_id, component_itemid, id, part_tree_id, componentid, name, lvl, tree_path, root_id, order1,
case when lvl - lead(lvl) over (order by order1) < 0 then 0 else 1 end is_leaf
from tree_view
)
where is_leaf = 1;下面是使用您提供的数据在Oracle上执行的示例：
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as sitja@orasitja
SQL>
SQL> col tree_path format a40
SQL> col lvl1_part format a20
SQL> col lvl2_part format a20
SQL> col lvl3_part format a20
SQL> drop table tree_entries;
Table dropped
SQL> create table tree_entries as
2 with tree_entries(part_tree_id, componentid, name) as (
3 select 1, 101, 'CLOCK' from dual union all
4 select 1, 102, 'WATCH' from dual union all
5 select 1, 105, 'BAND' from dual union all
6 select 1, 113, 'MATERIAL' from dual union all
7 select 1, 114, 'COLOR' from dual union all
8 select 1, 106, 'CASE' from dual union all
9 select 1, 115, 'MATERIAL' from dual union all
10 select 1, 116, 'SHAPE' from dual union all
11 select 1, 107, 'BEZEL' from dual union all
12 select 1, 117, 'MATERIAL' from dual union all
13 select 1, 118, 'TEXTURE' from dual union all
14 select 1, 108, 'FACE' from dual union all
15 select 1, 119, 'SHAPE' from dual union all
16 select 1, 120, 'DESIGN' from dual union all
17 select 2, 103, 'RELOJ' from dual union all
18 select 2, 104, 'RELOJPULSERA' from dual union all
19 select 2, 109, 'CORREA' from dual union all
20 select 2, 121, 'MATERIAL' from dual union all
21 select 2, 122, 'COLOR' from dual union all
22 select 2, 110, 'CAJA' from dual union all
23 select 2, 123, 'MATERIAL' from dual union all
24 select 2, 124, 'FORMA' from dual union all
25 select 2, 111, 'BISEL' from dual union all
26 select 2, 125, 'MATERIAL' from dual union all
27 select 2, 126, 'TEXTURA' from dual union all
28 select 2, 112, 'CARATULA' from dual union all
29 select 2, 127, 'FORMA' from dual union all
30 select 2, 128, 'DISEÑO' from dual
31 )
32 select * from tree_entries;
Table created
SQL> drop table tree_items;
Table dropped
SQL> create table tree_items as
2 with tree_items(tree_id, component_id, component_itemid, id) as (
3 select 1, 'A', 101, 1 from dual union all
4 select 1, 'A', 102, 2 from dual union all
5 select 1, '101', 107, 3 from dual union all
6 select 1, '101', 108, 4 from dual union all
7 select 1, '102', 105, 5 from dual union all
8 select 1, '102', 106, 6 from dual union all
9 select 1, '107', 117, 7 from dual union all
10 select 1, '107', 118, 8 from dual union all
11 select 1, '108', 119, 9 from dual union all
12 select 1, '108', 120, 10 from dual union all
13 select 1, '105', 113, 11 from dual union all
14 select 1, '105', 114, 12 from dual union all
15 select 1, '106', 115, 13 from dual union all
16 select 1, '106', 116, 14 from dual union all
17 select 2, 'A', 103, 15 from dual union all
18 select 2, 'A', 104, 26 from dual union all
19 select 2, '103', 111, 33 from dual union all
20 select 2, '103', 112, 42 from dual union all
21 select 2, '104', 109, 54 from dual union all
22 select 2, '104', 110, 62 from dual union all
23 select 2, '111', 125, 74 from dual union all
24 select 2, '111', 126, 82 from dual union all
25 select 2, '112', 127, 91 from dual union all
26 select 2, '112', 128, 10 from dual union all
27 select 2, '109', 127, 114 from dual union all
28 select 2, '109', 122, 122 from dual union all
29 select 2, '110', 123, 3334 from dual union all
30 select 2, '110', 124, 141 from dual
31 )
32 select * from tree_items;
Table created
SQL> with tree_view(tree_id, component_id, component_itemid, id, part_tree_id, componentid, name, lvl, tree_path, root_id) as (
2 select t.tree_id, t.component_id, t.component_itemid, t.id, e.part_tree_id, e.componentid, e.name, 1, e.name, component_itemid
3 from tree_items t,
4 tree_entries e
5 where t.component_itemid = e.componentid
6 and t.tree_id = e.part_tree_id
7 and t.component_id = 'A'
8 union all
9 select t.tree_id, t.component_id, t.component_itemid, t.id, e.part_tree_id, e.componentid, e.name, tv.lvl + 1, tv.tree_path || '=>' || e.name, tv.root_id
10 from tree_items t,
11 tree_entries e,
12 tree_view tv
13 where to_char(tv.component_itemid) = to_char(t.component_id)
14 and to_char(e.componentid) = to_char(t.component_itemid)
15 and tv.tree_id = t.tree_id
16 ) -- end of hierarchy view
17 search depth first by lvl set order1
18 select tree_path,
19 name,
20 componentid,
21 regexp_substr(tree_path, '[[:alpha:]]+', 1, 1) lvl1_part,
22 regexp_substr(tree_path, '[[:alpha:]]+', 1, 2) lvl2_part,
23 regexp_substr(tree_path, '[[:alpha:]]+', 1, 3) lvl3_part -- add more if there are further levels down the tree
24 from (
25 select tree_id, component_id, component_itemid, id, part_tree_id, componentid, name, lvl, tree_path, root_id, order1,
26 case when lvl - lead(lvl) over (order by order1) < 0 then 0 else 1 end is_leaf
27 from tree_view
28 )
29 where is_leaf = 1;
TREE_PATH NAME COMPONENTID LVL1_PART LVL2_PART LVL3_PART
---------------------------------------- ------------ ----------- -------------------- -------------------- --------------------
CLOCK=>BEZEL=>MATERIAL MATERIAL 117 CLOCK BEZEL MATERIAL
CLOCK=>BEZEL=>TEXTURE TEXTURE 118 CLOCK BEZEL TEXTURE
CLOCK=>FACE=>SHAPE SHAPE 119 CLOCK FACE SHAPE
CLOCK=>FACE=>DESIGN DESIGN 120 CLOCK FACE DESIGN
WATCH=>BAND=>MATERIAL MATERIAL 113 WATCH BAND MATERIAL
WATCH=>BAND=>COLOR COLOR 114 WATCH BAND COLOR
WATCH=>CASE=>MATERIAL MATERIAL 115 WATCH CASE MATERIAL
WATCH=>CASE=>SHAPE SHAPE 116 WATCH CASE SHAPE
RELOJ=>BISEL=>MATERIAL MATERIAL 125 RELOJ BISEL MATERIAL
RELOJ=>BISEL=>TEXTURA TEXTURA 126 RELOJ BISEL TEXTURA
RELOJ=>CARATULA=>FORMA FORMA 127 RELOJ CARATULA FORMA
RELOJ=>CARATULA=>DISEÑO DISEÑO 128 RELOJ CARATULA DISEÑO
RELOJPULSERA=>CORREA=>COLOR COLOR 122 RELOJPULSERA CORREA COLOR
RELOJPULSERA=>CORREA=>FORMA FORMA 127 RELOJPULSERA CORREA FORMA
RELOJPULSERA=>CAJA=>MATERIAL MATERIAL 123 RELOJPULSERA CAJA MATERIAL
RELOJPULSERA=>CAJA=>FORMA FORMA 124 RELOJPULSERA CAJA FORMA
16 rows selected
SQL> 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/36410849
复制相关文章
相似问题
腾讯云开发者
