使用 PL/SQL 检索层次结构中节点的根

发布于 2024-08-02 22:36:35 字数 1226 浏览 2 评论 0原文

我有一个简单的父/子类型视图，有两列：MYID 和 MYPARENTID。在 PL/SQL 中，获取父级的所有子级的列表非常简单：

SELECT MYID 
FROM MYVIEW 
START WITH MYID = 'TargetId1' 
CONNECT BY PRIOR MYID = MYPARENTID

我会得到如下结果：

MYID
-----------
TargetId1
TargetId1Child1
TargetId1Grandchild1

但现在假设我想一次性为一组父级执行此操作

SELECT MYID 
FROM MYVIEW 
START WITH MYID IN ('TargetId1', 'TargetId2', 'TargetId3') 
CONNECT BY PRIOR MYID = MYPARENTID

：结果看起来像这样：

MYID
---------
TargetId1
TargetId1Child1
TargetId1Grandchild1
TargetId2
TargetId2Child2
TargetId2Grandchild1
TargetId3
TargetId3Child3
TargetId3Grandchild1

当我这样做时，我失去了知道特定子节点来自哪里的能力。我获取了子项列表，但我想知道每个子项源自哪个根（本质上是 START WITH 值）；

我想要一个如下所示的结果集：

MYID                    ROOT
----------------------------------
TargetId1               TargetId1
TargetId1Child1         TargetId1
TargetId1Grandchild1    TargetId1
TargetId2               TargetId2
TargetId2Child2         TargetId2
TargetId2Grandchild2    TargetId2
TargetId3               TargetId3
TargetId3Child3         TargetId3
TargetId3Grandchild3    TargetId3

我该怎么做？

原文

I have a simple parent/child type view with two columns: MYID and MYPARENTID. In PL/SQL, getting a list of all of the children of a parent is simple enough:

SELECT MYID 
FROM MYVIEW 
START WITH MYID = 'TargetId1' 
CONNECT BY PRIOR MYID = MYPARENTID

And I would get something back like this:

MYID
-----------
TargetId1
TargetId1Child1
TargetId1Grandchild1

But now let's say that I want to do this for a set of parents, all at once:

SELECT MYID 
FROM MYVIEW 
START WITH MYID IN ('TargetId1', 'TargetId2', 'TargetId3') 
CONNECT BY PRIOR MYID = MYPARENTID

My result looks like this:

MYID
---------
TargetId1
TargetId1Child1
TargetId1Grandchild1
TargetId2
TargetId2Child2
TargetId2Grandchild1
TargetId3
TargetId3Child3
TargetId3Grandchild1

When I do it this way, I lose the ability to know where a particular child node came from. I get back a list of children, but I want to know which root (essentially, the START WITH value) each child originated from;

I want a result set that looks like this:

MYID                    ROOT
----------------------------------
TargetId1               TargetId1
TargetId1Child1         TargetId1
TargetId1Grandchild1    TargetId1
TargetId2               TargetId2
TargetId2Child2         TargetId2
TargetId2Grandchild2    TargetId2
TargetId3               TargetId3
TargetId3Child3         TargetId3
TargetId3Grandchild3    TargetId3

How can I do this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

长发绾君心 2024-08-09 22:36:35

我自己还没有使用过它，但根据我对你的问题的理解，我认为运算符 CONNECT_BY_ROOT 就是你正在寻找的。但需要 Oracle 10g。

SELECT MYID
      ,CONNECT_BY_ROOT MYID ROOT
FROM MYVIEW 
START WITH MYID IN ('TargetId1', 'TargetId2', 'TargetId3') 
CONNECT BY PRIOR MYID = MYPARENTID

I have not used it myself yet, but from my understanding of your question, I would think that the operator CONNECT_BY_ROOT is what you are looking for. Requires Oracle 10g, though.

SELECT MYID
      ,CONNECT_BY_ROOT MYID ROOT
FROM MYVIEW 
START WITH MYID IN ('TargetId1', 'TargetId2', 'TargetId3') 
CONNECT BY PRIOR MYID = MYPARENTID

回复收藏 0 原文

哥，最终变帅啦 2024-08-09 22:36:35

我不知道 PL/SQL，但您也许能够：

将 TargetId 列表插入临时表
将临时表与视图连接

起来

CREATE TABLE #Parents (TargetId)

INSERT INTO #Parents TargetId1
INSERT INTO #Parents TargetId2
...
INSERT INTO #Parents TargetId10

SELECT MYID, #Parents.TargetID 
FROM MYVIEW, #Parents
CONNECT BY MYID = #Parents.TargetID
CONNECT BY PRIOR MYID = MYPARENTID

I don't know PL/SQL, but you might be able to:

Insert the TargetId list into a temp table
Join the temp table with the view

E.g.

CREATE TABLE #Parents (TargetId)

INSERT INTO #Parents TargetId1
INSERT INTO #Parents TargetId2
...
INSERT INTO #Parents TargetId10

SELECT MYID, #Parents.TargetID 
FROM MYVIEW, #Parents
CONNECT BY MYID = #Parents.TargetID
CONNECT BY PRIOR MYID = MYPARENTID

回复收藏 0 原文

~没有更多了~