递归选择帮助 [英] Help with recursive SELECT

问题描述

这是情况.我有两个桌子:

Here's the situation. I have two tables:

• 用户( 网站)，
• 消息(彼此之间发送的个人消息)

• users (registered users of the website),
• messages (personal messages they sent between each other)

邮件表具有以下列(仅是重要的列):

The messages table has these columns (just the important ones):

• id，
• 发件人(发送 消息)，
• 该用户的接收者ID 邮件已发送)，
• reply_to(此消息所针对的消息的ID 回复，可以为NULL)

• id,
• sender (id of user who sent the message),
• receiver id of user to whom the message was sent),
• reply_to (id of a message to which this message is reply to, can be NULL)

我需要做的是构造一个SELECT查询，该查询将选择2个用户之间的完整对话. IE.如果用户A回复了用户B发送的消息，并且用户B回复了该消息，我想得到三行，如下所示:

What I need to do is construct a SELECT query that will select a complete conversation between 2 users. I.e. if user A replies to message sent from user B and the user B replies back to the message, I would like to get three rows like this:

• message03:回复message02
• message02:回复message01
• 从用户A到用户B的message01

我确定可以基于reply_to字段构造这样的SELECT查询，但是我之前从未做过类似的事情，所以我需要一点帮助.

I'm sure that it is possible to construct such a SELECT query based on the reply_to field but I have never done something like it before so I need a little help.

SELECT查询应针对MySQL数据库.

The SELECT query should be for MySQL database.

推荐答案

实际上您是错误的:使用ANSI SQL不可能 .某些具有供应商扩展名的数据库(例如Oracle的CONNECT BY)也许可以执行您想要的操作，但不能执行普通的旧SQL.

Actually you're incorrect: with ANSI SQL this isn't possible. Certain databases with vendor extensions (eg Oracle's CONNECT BY) may be able to do what you want but not plain old SQL.

我的建议?更改数据，以便提供更简单的解决方案.

My advice? Change your data so enable an easier solution.

在这种情况下，为每条消息指定一个session_id.如果用户发布新消息，请提供一个新值(当前未使用).如果他们回复，请保留要回复的邮件的session_id.

In this case, give each message a conversation_id. If the user posts a new message, give that a new (currently unused) value. If they reply, keep the conversation_id of the message being replied to.

然后查询数据变得很简单.

Then querying the data becomes trivial.

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