如何根据可以位于两列中任意一列的用户 ID 仅选择最新记录？

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，即他们正在与谁聊天。如果您愿意，可以将其单独创建为视图，以简化查询。

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

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

Answer 2

听起来您想要类似的内容：

SELECT MAX(date), * FROM messages WHERE to IN (
    SELECT DISTINCT to FROM messages WHERE from = :id
) GROUP BY to
UNION
SELECT MAX(date), * FROM messages WHERE from IN (
    SELECT DISTINCT from FROM messages WHERE to = :id
) GROUP BY from
ORDER BY 1

其中 id 是您用于用户 ID 的参数。

Answer 3

您可以创建一个视图并查询它：

CREATE VIEW dataview AS 
(SELECT t1.from AS userid, t1.date FROM messages AS t1) 
UNION
(SELECT t2.to AS userid, t2.date FROM messages AS t2)

查看结果表，现在应该很容易查询您想要的内容

Answer 4

我想你可能想要这样的东西：

SELECT MAX(date)
FROM messages
WHERE from = $userid
   OR to   = $userid

你可以用实际值替换 $userid ，希望使用 准备好的声明。 :-)