mysql - 如何根据可以在两列中的任意一列中的用户标识仅选择最近的记录？

Question

我有一个messages 表，它的结构有点像这样:

from  |  to  |  date
-----------------------------------
1     |  3   |  2011-09-23 11:51:44
3     |  1   |  2011-09-23 11:56:29
3     |  2   |  2011-10-04 10:20:01
2     |  3   |  2011-10-05 07:48:00

我想在我的网站上显示一个与 Facebook 的消息页面非常相似的消息页面，该页面显示与用户进行对话的人员列表。无论对话的深度如何，该页面只显示每个人一次，以及与该特定人的对话中的最新消息，无论是发送还是接收。

让我难过的部分是最近的消息既可以发送也可以接收，这意味着用户的 ID 号可以在 from 或 to 中柱子。我不确定如何按照我需要的方式针对这两列进行测试。

我仍在学习如何编写更复杂的 MySQL 查询，虽然我觉得这是将 OR 与子查询一起使用的简单案例，但我似乎无法做到正确。

解决方案事实证明，这根本不是一个非常简单的案例。 Widor 花了一些时间来帮助我解决这个问题，下面的查询似乎终于完成了这项工作。它尚未经过彻底测试，但到目前为止它似乎工作正常:

SELECT m.*
FROM   messages m
       JOIN (SELECT Max(x.id)      AS `id`,
                    x.userid,
                    x.partnerid,
                    Max(x.mostrecent) AS `mostrecent`
             FROM  (SELECT Max(id)     `id`,
                           `from`         AS `userid`,
                           `to`           AS `partnerid`,
                           Max(`created`) AS `mostrecent`
                    FROM   messages
                    GROUP  BY `from`,
                              `to`
                    UNION
                    SELECT Max(id)     `id`,
                           `to`           AS `userid`,
                           `from`         AS `partnerid`,
                           Max(`created`) AS `mostrecent`
                    FROM   messages
                    GROUP  BY `to`,
                              `from`) AS `x`
             GROUP  BY x.`userid`,
                       x.`partnerid`) AS `y`
         ON y.id = m.id
WHERE  y.userid = $userid

Answer 1

重新编辑

我之前的回答(和这里的其他一些回答一样)没有考虑到您有超过 2 个对话“伙伴”的情况，这在您的示例数据中不是这种情况，但我相信会在现实生活。

因此请考虑您现在在数据中有一条额外记录的情况:

1    |     4    | 2011-10-04 08:34:12

我修改后的查询如下:

SELECT userid, partnerid, max(mostRecent) from (
    SELECT [from] as [userid], [to] as [partnerid], max([date]) as [mostrecent] FROM messages GROUP BY [from], [to]
    UNION 
    SELECT [to] as [userid], [from] as [partnerid],max([date]) as [mostrecent] FROM messages GROUP BY [to], [from]
) [x]
WHERE userid = ?
GROUP BY userid, partnerid

因此，我们内部的 UNION 为我们提供了一个数据集，其中包含 userid 以及 partnerid，即他们正在与谁聊天。如果需要，可以将其单独创建为 View ，以简化查询。

然后，外部 SELECT 为指定的 userid 与之聊天的每个“伙伴”检索一条记录，以及最近的日期。

Max() 函数获取最新日期，GROUP BY 负责确保我们为每个合作伙伴带回不止一条记录。