甲骨文由多个父母连接 [英] oracle connect by multiple parents
问题描述
我在使用connect by时遇到问题.
I am facing an issue using connect by.
我有一个查询，通过该查询可以检索包括以下三列的几列:
I have a query through which I retrieve a few columns including these three:
- ID
- ParentID
- ObjectID
对于相同的ID和parentID，现在存在多个关联的对象，例如
Now for the same ID and parentID, there are multiple objects associated e.g.
ID父ID对象ID
1 0 112
1 0 113
2 0 111
2 0 112
3 1个 111
4 1个 112
ID ParentID ObjectID
1 0 112
1 0 113
2 0 111
2 0 112
3 1 111
4 1 112
我正在尝试使用connect by，但是无法以适当的层次结构获取结果.我需要它如下所示的方式.取一个ID-parentID组合，显示带有该ID-parentID的所有行，然后显示该ID的所有子代，即其parentID = ID的所有子代
ID父ID ObjectID
1 0 112
1 0 113
3 1个 111
4 1个 112
2 0 111
2 0 112
I am trying to use connect by but I'm unable to get the result in a proper hierarchy. I need it the way it is showed below. Take an ID-parentID combo, display all rows with that ID-parentID and then all the children of this ID i.e. whose parentID=ID
ID ParentID ObjectID
1 0 112
1 0 113
3 1 111
4 1 112
2 0 111
2 0 112
select ID,parent_id, object_id from table start with parent_id=0
connect by prior id=parent_id order by id,parent_id
上面的查询没有导致我需要的适当层次结构.
Above query is not resulting into proper hierarchy that i need.
推荐答案
首先感谢所有尝试帮助我的人.
First of all Thanks to all who tried helping me.
最后，我更改了方法，将层次结构CONNECT BY子句应用于内部查询，但多次连接对我不起作用.
Finally i changed my approach as applying hierarchy CONNECT BY clause to inner queryw ith multiple joins was not working for me.
我采取了以下方法
-
从First表(即具有ID-ParentID的表)中获取层次结构数据.使用CONNECT BY选择Query table1.它将以正确的顺序给出ID. 加入检索到的ID列表.
Get the hierarchical data from First table i.e. table with ID-ParentID. Select Query table1 using CONNECT BY. It will give the ID in proper sequence. Join the retrieved List of ID.
将上述ID作为选择查询IN子句中的逗号分隔字符串传递给具有ID-ObjectID的第二个表.
Pass the above ID as comma seperated string in select query IN Clause to second table with ID-ObjectID.
从table2中选择*，其中ID的顺序(在ID的连接字符串上方)按以下顺序排列 instr('ID的以上连接字符串'，ID);
select * from table2 where ID in (above Joined string of ID) order by instr('above Joined string of ID',ID);
ORDER BY INSTR发挥了魔力.它将给我按IN子句数据排序的结果，并使用分层查询准备IN子句字符串.因此，它将显然是按顺序进行的.
ORDER BY INSTR did the magic. It will give me the result ordered by the IN Clause data and IN Clause string is prepared using the hierarchical query. Hence it will obviously be in sequence.
再次感谢大家的帮助！
注意:上面的方法有一个约束:ID作为IN子句中的逗号分隔的字符串传递. IN子句中的字符数有限制.我猜是1000个字符.没有把握. 但是，正如我在First表的数据上确定的那样，它不会超过1000个字符的限制.因此，我选择了上述方法.
Note: Above approach has one constraint : ID passed as comma separated string in IN Clause. IN Clause has a limit of characters inside it. I guess 1000 chars. Not sure. But as i am sure on the data of First table that it will not be so much so as to cross limit of 1000 chars. Hence i chose above approach.
这篇关于甲骨文由多个父母连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
