mysql - 根据两个条件选择最近的记录

Question

我有 user1 与 user2 和 user4 交换消息(这些参数是已知的)。我现在想为每个对话选择最新发送或接收的消息(即每个对话 LIMIT 1)。

SQLFiddle

目前我的查询返回所有对话的所有消息:

SELECT * 
FROM message 
WHERE (toUserID IN (2,4) AND userID = 1)
OR    (userID IN (2,4) AND toUserID = 1)
ORDER BY message.time DESC

返回的行应该是 messageID 3 和 6。

Answer 1

假设较高的 id 值表示较新的消息，您可以这样做:

• 查找所有涉及用户 1 的消息
• 按其他用户 ID 对结果进行分组
• 获取每个组的最大消息id

SELECT *
FROM message
WHERE messageID IN (
    SELECT MAX(messageID)
    FROM message
    WHERE userID = 1 -- optionally filter by the other user
    OR toUserID = 1 -- optionally filter by the other user
    GROUP BY CASE WHEN userID = 1 THEN toUserID ELSE userID END
)
ORDER BY messageID DESC

Updated SQLFiddle