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

I have user1 who exchanged messages with user2 and user4 (these parameters are known). I now want to select the latest sent or received message for each conversation (i.e. LIMIT 1 for each conversation).

我有user1与user2和user4交换消息(这些参数是已知的)。现在，我想为每个会话选择最近发送或接收的消息(例如，每个会话的限制为1)。

SQLFiddle

SQLFiddle

Currently my query returns all messages for all conversations:

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

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

The returned rows should be messageID 3 and 6.

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

4 个解决方案

#1

Assuming that higher id values indicate more recent messages, you can do this:

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

Find all messages that involve user 1
查找所有涉及用户1的消息
Group the results by the other user id
使用其他用户id对结果进行分组
Get the maximum message id per group
获取每个组的最大消息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

更新SQLFiddle

#2

You can do this easily by separating it into two queries with ORDER BY and LIMIT then joining them with UNION:

你可以很容易地做到这一点，把它分成两个查询，按顺序排列，然后限制它们与UNION连接:

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

The parenthesis are important here, and this returns messages 2 and 6, which seems correct, not 3 and 6.

括号在这里很重要，它返回消息2和6，看起来是正确的，而不是3和6。

It also seems like you could use UNION ALL for performance instead of UNION because there won't be duplicates between the two queries, but it's better if you decide that.

看起来你也可以使用UNION ALL for performance而不是UNION，因为这两个查询之间不会有重复，但是如果你决定了就更好了。

Here's your data:

这是你的数据:

MESSAGEID  USERID   TOUSERID    MESSAGE           TIME
1          1        2           nachricht 1       123
2          1        2           nachricht 2       124
3          2        1           nachricht 3       125
4          3        2           nachricht wrong   1263
5          2        4           nachricht wrong   1261
6          4        1           nachricht sandra  126

#3

The below works as required:

以下工作按要求进行:

SELECT  m1.*
FROM    Message m1
        LEFT JOIN Message m2
            ON LEAST(m1.toUserID, m1.userID) = LEAST(m2.toUserID, m2.userID)
            AND GREATEST(m1.toUserID, m1.userID) = GREATEST(m2.toUserID, m2.userID)
            AND m2.time > m1.Time
WHERE   m2.MessageID IS NULL
AND (   (m1.toUserID IN (2,4) AND m1.userID = 1)
    OR  (m1.userID IN (2,4) AND m1.toUserID = 1)
    );

To simplify how this works, imagine you just wanted the latest message sent by userid 1, rather than having to match the to/from tuples as this adds clutter to the query that doesn't help. To get this I would use:

为了简化它的工作方式，假设您只是想要userid 1发送的最新消息，而不是必须匹配To /from元组，因为这样会给查询增加一些不必要的混乱。为了得到这个，我将使用:

SELECT  m1.*
FROM    Message AS m1
        LEFT JOIN Message AS m2
            ON m2.UserID = m1.UserID
            AND m2.time > m1.time
WHERE   m1.UserID = 1
AND     m2.MessageID IS NULL;

So, we are joining similar messages, stipulating that the second message (m2) has a greater time than the first, where m2 is null it means there is not a similar message with a later time, therefore m2 is the latest message.

因此，我们加入了类似的消息，规定第二个消息(m2)的时间大于第一个消息(m2)，其中m2为null，这意味着后面没有类似的消息，因此m2是最新的消息。

Exactly the principal has been applied in the solution, but we have a more complicated join to link conversations.

在解决方案中已经应用了原则，但是我们有一个更复杂的连接来连接对话。

I have used LEAST and GREATEST in the join, the theory being that since you have 2 members in your tuple (UserID, ToUserID), then in any combination the greatest and the least will be the same, e.g.:

我在join中使用的最小和最大，理论是既然您的tuple (UserID, ToUserID)中有两个成员，那么在任何组合中最大和最小都是相同的，例如:

From/To |  Greatest | Least |
--------+-----------+-------+
  1, 2  |     2     |   1   |
  2, 1  |     2     |   1   |
  1, 4  |     4     |   1   |
  4, 1  |     4     |   1   |
  4, 2  |     4     |   2   |
  2, 4  |     4     |   2   |

As you can see, in similar From/To the greatest and the least will be the same, so you can use this to join the table to itself.

正如您所看到的，从最大到最大，最小都是相同的，因此您可以使用它将表连接到自己。

#4

There are two parts of your query in the following order:

您的查询有以下两个部分:

You want the latest outgoing or incoming message for a conversation between two users
您希望为两个用户之间的对话提供最新的传出或传入消息
You want these latest messages for two different pairs of users, i.e. conversations.
您需要为两对不同的用户(即会话)提供这些最新消息。

So, lets get the latest message for a conversation between UserID a and UserID b:

那么，让我们获取UserID a和UserID b之间对话的最新消息:

SELECT *
FROM message 
WHERE (toUserID, userID) IN ((a, b), (b, a)) 
ORDER BY message.time DESC
LIMIT 1

Then you want these to be combined for the two conversations between UserIDs 1 and 2 and UserIDs 1 and 4. This is where the union comes into play (we do not need to check for duplicates, thus we use UNION ALL, thanks to Marcus Adams, who brought that up first).

然后你想要把它们结合在一起，在userids1和2和UserIDs 1和4之间进行对话。这就是union起作用的地方(我们不需要检查副本，因此我们使用union ALL，这要感谢首先提出这个问题的Marcus Adams)。

So a complete and straightforward solution would be:

所以一个完整而直接的解决方案是:

(SELECT *
FROM message 
WHERE (toUserID, userID) IN ((2, 1), (1, 2)) 
ORDER BY message.time DESC
LIMIT 1)
UNION ALL
(SELECT * 
FROM message 
WHERE (toUserID, userID) IN ((4, 1), (1, 4)) 
ORDER BY message.time DESC
LIMIT 1)

And as expected, you get message 3 and 6 in your SQLFiddle.

正如预期的那样，您将在SQLFiddle中获得消息3和6。

#1