SQL，查找两个人之间最近的对话记录

Question

我有一张名为Messages的表格，它的格式如下图所示：

这张表储存了人与人之间发送的所有消息，但我需要像Facebook的消息系统那样进行设置。

为此，我的查询应该是这样的：

SELECT * FROM Messages WHERE To_User = x OR From_User = x ORDER BY Time DESC

这将获取到我和x用户之间收发的所有消息，并按时间顺序排序，最新的消息最先出现。

然而，Facebook的系统像一个论坛一样进行排序，因此在任何给定的查询结果中，我只想要涉及到y用户的一条消息。所以我不想看到这种情况发生：

To_User: 6 From_User: 7 Status: I agree with your message Time: 12345

To_User: 7 To_User: 6 Status: Do you agree with my message? Time: 12344

比方说我正在为用户6展示消息页面，使用当前系统会展示两条消息。相反，只有顶部的那个应该被选择，因为我只想显示用户6和7之间最新的消息，而不是每一个消息。

通常来说，这通常只需要一个LIMIT语句就可以解决，但我需要能够获取大量的消息，但唯一的规则是：

1.它必须是来自或发往用户X 2.不得选择来自或发往用户Y的两条消息 3.所选的消息必须是用户X和Y之间的最新消息

这很难描述，但我希望我已经描述得足够详细了，以便得到解决方案。

我已经考虑过使用GROUP BY，但我无法让它考虑到需要找到来自或发往我的消息而不只是仅限于发件人或收件人的情况。

如果问题没有描述得足够详细，请在下投票之前告诉我，我会尽力描述得更清楚。

"给Jane Doe" "你好"

"来自John Doe" "你的留言很有趣"

"给John Doe" "我发出的信息"

然而，我不想展示除我和John Doe之外的其他信息，但由于我在查看表格中的两个不同列（From和To）以确定我和John Doe是否相关，因此我认为普通的GROUP BY语句行不通。

现在，如果另外还有Bob Joe给我发了一条消息，它可能会像这样：

"来自Bob Joe" "你好"

"给Jane Doe" "你好"

"来自John Doe" "你的留言很有趣"

- Ashley Davies

---

我假设你正在使用MySQL，对吗？ - Mark Byers

我无法理解这个问题。:| LIMIT有什么问题吗？你能给我们一个真实的用例示例吗？ - Cranio

我认为我理解了这个问题，但我同意Cranio的观点。如果您提供一个实际的示例来说明您正在尝试做什么，那么理解您的问题将会容易17倍。例如输入、预期输出。甚至可以为我们创建一个sqlfiddle。 - Mark Byers

我会举个例子，给我一点时间编辑。 - Ashley Davies

Answer 1

您可以自连接表并使用外连接，以便任何“早期”的消息都会“过滤掉”任何“非早期”的消息。

SELECT * FROM Messages main LEFT JOIN Messages earlier
    ON earlier.time < main.time AND 
        (
            earlier.To_User = main.To_User AND earlier.From_User = main.From_User OR  
            earlier.To_User = main.From_User AND earlier.From_User = main.To_User
        )
WHERE (main.To_User = x OR main.From_User = x) AND earlier.id IS NULL
ORDER BY main.Time DESC

- Jirka Hanika

---

谢谢，我现在会尝试一下，如果有效的话，我会接受的，看起来应该可以。 - Ashley Davies

@AshleyDavies - 我看不出这个不起作用的余地。你使用的数据库引擎是什么？如果你移除 earlier.id IS NULL 的检查，你会看到什么数据？你是否使用完全相同的查询，还是将其嵌入到更大的东西中？你如何提供 x？ - Jirka Hanika

@AshleyDavies - MySQL中的列名不区分大小写，但是在某些操作系统上，表名可能会区分大小写。关于PHP问题，如果您在查询中将*替换为main.*，我认为这将有所帮助。也许您甚至需要在PHP中打印特定的列。 - Jirka Hanika

@AshleyDavies - 这里有另外一种解决方法：http://www.webhostingtalk.com/archive/index.php/t-420807.html - Jirka Hanika

@AshleyDavies - 因为你忘记把 earlier.id IS NULL 放回去了。 - Jirka Hanika

Answer 2

如果你能消除一个人出现在“from”或“to”列中的复杂性，那么这将是查找组内最小行的常见问题实例。因此，你可以从以下内容开始：

SELECT id, time, to_user as user
UNION ALL SELECT id, time, from_user as user

然后将该查询的结果用作任何标准 最小行技术 的输入。

- gcbenison

---

不幸的是，我无法将它们从其中一个中清除，我需要跟踪它们，以便知道谁发送了个人消息。 - Ashley Davies

我并不是指要更改您原来的表结构；我的意思是使用该SELECT查询的结果作为最小行技术的输入。 - gcbenison

啊，误解了你的回答，抱歉。 - Ashley Davies