使用oracle connect by查找邻接表模型中的所有节点 [英] Find all nodes in an adjacency list model with oracle connect by

问题描述

给出以下模型:

create table child_parent (
  child number(3),
  parent number(3)
);

给出以下数据:

insert into child_parent values(2,1);
insert into child_parent values(3,1);
insert into child_parent values(4,2);
insert into child_parent values(5,2);
insert into child_parent values(6,3);

得出以下树:

        1
       / \
      2   3
     / \   \
    4   5   6

现在我可以找到5个这样的父母了:

Now i can find the parents of 5 like this:

SELECT parent FROM child_parent START WITH  child = 5 
              CONNECT BY NOCYCLE PRIOR parent = child;

但是如何从5开始获得所有节点(1,2,3,4,5,6)?

But how can I get all the nodes (1,2,3,4,5,6) starting from 5?

推荐答案

Oracle的CONNECT BY语法旨在遍历分层数据:它是单向的，因此不适合表示需要双向性的图.不能在一个查询中使用2 -> 1 -> 3，这是使所有节点从5开始的必需操作.

Oracle's CONNECT BY syntax is intended for traversing hierarchical data: it is uni-directional so not a suitable for representing a graph, which requires bi-directionality. There's no way to go 2 -> 1 -> 3 in one query, which is what you need to do to get all the nodes starting from 5.

很久以前，我回答了一个关于平整层次结构中的节点的问题(即AKA传递闭包)，即，如果1->2->3为true，则'1-> 3'也为true.它链接到一篇论文，该论文演示了PL/SQL解决方案以生成所有边缘并将它们存储在表中.在这种情况下，可以使用类似的解决方案.但是很明显，只有图中的节点不经常更改才是可行的.因此，也许只有有限的用途.无论如何，查找更多信息.

A long timne ago I answered a question on flattening nodes in a hierarchy (AKA transitive closure) i.e. if 1->2->3 is true, `1->3' is true as well. It links to a paper which demonstrates a PL/SQL solution to generate all the edges and store them in a table. A similar solution could be used in this case. But obviously it is only practical if the nodes in the graph don't chnage very often. So perhaps it will only be of limited use. Anyway, find out more.

这篇关于使用oracle connect by查找邻接表模型中的所有节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！