Oracle中的递归查询 [英] Recursive query in Oracle

问题描述

我对PLSQL的更高级主题还是陌生的，所以希望有人可以帮助我.

I am kind of new to the more advanced topics of PLSQL, so hopefully someone can help me out.

问题: 我有一个表格，其中包含管理员和用户之间发送的消息.该表在同一表的message_id字段中具有带FK的message_parent:如果填充了该字段，则意味着该消息是作为对先前消息的答复而发送的.我需要选择属于同一对话的所有消息并显示它们.可以通过单个查询完成此操作，还是需要一个过程来处理这种逻辑?据我了解，它必须是递归的，因为我正在搜索的message_id总是在变化

The problem: I have a table with messages sent between an admin and users. The table has a message_parent with FK to the same table message_id field: in case the field is populated, then it means that message was sent as a reply to a previous message. I need to select all the messages that are part of the same conversation and display them. Can this be done with a single query or do I need a procedure to handle that kind of logic? As I understand, it needs to be recursive, since the message_id by which I am searching, is always changing

示例 消息表:

|message_id|parent_id|message_content|
|----------|---------|---------------|
|101       |100      | foo           |
|100       |97       | bar           |
|99        |(null)   | Left out      |
|97        |(null)   | baz           |

因此选择message_content的正确查询应返回"baz"，"bar"和"foo"，但不返回"Left out"(因为baz是原始消息). 如果有只能将两条消息捆绑在一起，例如一个thread_id列，可以将所有消息链接到同一个线程"中，但是随着parent_id不断变化，我很难弄清楚它.

So the correct query selecting message_content should return "baz", "bar" and "foo" but not "Left out" (since baz is the original message). This would be simple if there were e.g. only two messages that can be tied together or e.g. a thread_id column, that would link all messages in the same 'thread', but with the parent_id's constantly shifting, I am having trouble figuring it out.

推荐答案

在Oracle中，使用CONNECT BY

In Oracle this is easily done using CONNECT BY

select message_id, parent_id, message_content
from messages
start with message_id = 97 -- this is the root of your conversation
connect by prior message_id = parent_id;

这使树从上到下走动.

This walks the tree from top to bottom.

如果要将树从一条消息移到根，请更改start with和connect by部分:

If you want to walk the tree from a single message to the root, change the start with and the connect by part:

select message_id, parent_id, message_content
from messages
start with message_id = 100 -- this is the root of your conversation
connect by prior parent_id = message_id; -- this now goes "up" in the tree

这篇关于Oracle中的递归查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！