MySQL，如何从消息列表中选择对话

Question

我有这个代码:

SELECT a.id, a.to_id, a.from_id, a.seen, a.date, a.message

FROM  `Chat_messages` a

INNER JOIN (
    SELECT MAX(  `id` ) AS id
    FROM  `Chat_messages` AS  `alt` 
    WHERE  `alt`.`to_id` =7
    OR  `alt`.`from_id` =7
    GROUP BY  `to_id` ,  `from_id`
)b ON a.id = b.id

返回:

所以，我想获取用户的对话(发送和接收的消息)和它的最新消息。最新消息工作正常，但问题是我从收到的消息(#1 和#2)中得到 2 行，从发送的消息中得到 2 行(#3 和#4)，但我只需要 2 个结果，因为有 2 个对话.

Answer 1

分别挑选出包含最新发送消息的行和包含最新接收消息的行的最佳方法是使用 row_number() 窗口函数。不幸的是，MySql 不支持窗口函数，所以我认为最好使用两个嵌套的 SELECT:

SELECT z.id, max(z.to_id), max(z.from_id), max(z.seen), max(z.date), max(z.message)
FROM chat_messages z
LEFT JOIN 
(SELECT x.from_id, max(date) date
FROM chat_messages x
GROUP BY x.from_id) f
ON z.from_id = f.from_id AND z.date = f.date
LEFT JOIN
(SELECT y.to_id, max(date) date
FROM chat_messages y
GROUP BY y.to_id) t
ON z.to_id = t.to_id AND z.date = t.date
GROUP BY z.id

如果您关心长期的正确性，我不建议在 ID 上使用最大值。