给定一条消息的入口点的 SQL 2008 消息线程递归查询

发布于 2024-10-08 03:41:53 字数 690 浏览 2 评论 0原文

也许是一个过于复杂的标题。基本上可以想象一个电子邮件收件箱。我有一张桌子如下：

alt text

正如你所看到的，这是一个递归表，非常简单，只有一条消息的parentID ，正如您通过绿色突出显示环所看到的，当parentID 为NULL 时，“链”就结束了。

我需要的是提供（例如）INBOXID 12，并返回所有父项。在这个示例中，我应该返回 1 条记录，即 INBOXID 11。
在第二个示例中，我应该能够传入 INBOXID 9，但这次我应该返回行 INBOXID 8,7 和 1

我确实通过以下查询取得了一些成功：

with q as
(
select inboxid, parentid
from bizzbox
union all
select a.inboxid, a.parentid
from bizzbox a
inner join q on q.inboxID = a.parentID
)
select distinct * from q

..但当然它返回了所有任何行的父级..我知道这可能是非常愚蠢的简单的事情，比如其中一个选择上的 where 子句..但是尝试过（即参数化起始点 inboxid 的传入），我不能很清楚我需要做什么？？？

任何帮助非常感谢！！！！大卫.

原文

Maybe an over elaborate title. Basically think of an email inbox. I have a table
as so:

alt text

As you can see, it is a recursive table, very simple, just has the parentID of a message, and as you can see with the green highlight ring, the end of the "chain" is when there is a NULL for the parentID.

What I need is to provide (for example) the INBOXID of 12, and return back all parents.. in this example I should get 1 record back which is INBOXID of 11.
In the second example, I should be able to pass in INBOXID of 9, but this time I should get back rows INBOXID 8,7 and 1

I did have some success with the following query:

with q as
(
select inboxid, parentid
from bizzbox
union all
select a.inboxid, a.parentid
from bizzbox a
inner join q on q.inboxID = a.parentID
)
select distinct * from q

.. but of course it returns all of the parents for any of the rows.. I know it is probably something really stupidly simple like a where clause on one of the selects.. but having tried it (i.e. to parameterize the passing in of the start point inboxid), I can't quite see what I need to do???

Any help much appreciated!!!!!
David.

分享到QQ

分享到微博

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

发布评论

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

忘羡 2024-10-15 03:41:53

尝试用这个：

WITH  cte
    AS ( SELECT   InboxID,
                  ParentID
         FROM     BIZZBOX
         WHERE    InboxID = @inboxID
         UNION ALL
         SELECT   prev.InboxID,
                  prev.ParentID
         FROM     BIZZBOX prev
         INNER JOIN cte curr ON prev.InboxID = curr.ParentID ),
     cte1
   AS ( SELECT   InboxID,
                 ParentID
        FROM     BIZZBOX
        WHERE    InboxID = @inboxID
        UNION ALL
        SELECT   prev.InboxID,
                 prev.ParentID
        FROM     BIZZBOX prev
        INNER JOIN cte1 curr ON prev.ParentID = curr.InboxID )
SELECT  * FROM    cte
UNION
SELECT  * FROM    cte1

Try with this:

WITH  cte
    AS ( SELECT   InboxID,
                  ParentID
         FROM     BIZZBOX
         WHERE    InboxID = @inboxID
         UNION ALL
         SELECT   prev.InboxID,
                  prev.ParentID
         FROM     BIZZBOX prev
         INNER JOIN cte curr ON prev.InboxID = curr.ParentID ),
     cte1
   AS ( SELECT   InboxID,
                 ParentID
        FROM     BIZZBOX
        WHERE    InboxID = @inboxID
        UNION ALL
        SELECT   prev.InboxID,
                 prev.ParentID
        FROM     BIZZBOX prev
        INNER JOIN cte1 curr ON prev.ParentID = curr.InboxID )
SELECT  * FROM    cte
UNION
SELECT  * FROM    cte1

回复收藏 0 原文

~没有更多了~