MySQL 查询将消息分组到对话中并在收件箱中显示第一个

Question

例如，两个用户通过直接消息交谈，你们将进行消息对话。我只想从该对话中选择最新的消息，然后将其作为对话链接显示在他们的消息收件箱中......如何Facebook 和 Twitter 消息有效。然后他们可以单击最后发送的消息以查看整个对话。

包含用户之间发送的所有消息的我的消息表采用以下格式:

sourceUserId 是发送消息的用户id，targetUserId 是接收消息的用户id，body 是消息，time 是消息发送时间的时间戳。我将 body 保留为 abc... 并将时间保留为 1234 以简化此示例，它们都是不同的值。

+----+--------------+--------------+--------+------+
| id | sourceUserId | targetUserId |  body  | time |
+----+--------------+--------------+--------+------+
| 1  |       1      |       2      | abc... | 1234 |
| 2  |       3      |       1      | abc... | 1234 |
| 3  |       3      |       1      | abc... | 1234 |
| 4  |       1      |       3      | abc... | 1234 |
| 5  |       2      |       1      | abc... | 1234 |
| 6  |       1      |       2      | abc... | 1234 |
| 7  |       3      |       1      | abc... | 1234 |
| 8  |       4      |       1      | abc... | 1234 |
| 9  |       5      |       4      | abc... | 1234 |
| 10 |       3      |       2      | abc... | 1234 |
+----+--------------+--------------+--------+------+

要获取一个用户的所有消息(发送和接收)，我使用此查询:

SELECT sourceUserId, targetUserId, body,  UNIX_TIMESTAMP(time)  
FROM `usermessages`
WHERE targetUserId = 1
OR sourceUserId = 1
ORDER BY id DESC
LIMIT 10

+----+--------------+--------------+--------+------+
| id | sourceUserId | targetUserId |  body  | time |
+----+--------------+--------------+--------+------+
| 1  |       1      |       2      | abc... | 1234 |
| 2  |       3      |       1      | abc... | 1234 |
| 3  |       3      |       1      | abc... | 1234 |
| 4  |       1      |       3      | abc... | 1234 |
| 5  |       2      |       1      | abc... | 1234 |
| 6  |       1      |       2      | abc... | 1234 |
| 7  |       3      |       1      | abc... | 1234 |
| 8  |       4      |       1      | abc... | 1234 |
+----+--------------+--------------+--------+------+

但它会返回同一消息对话的多个实例，而不仅仅是来自两个用户之间对话的最新消息。例如，第 2、3 和 4 行都将显示完全相同的对话。

我可以使用以下查询使查询仅针对 targetUserId(用户收到的消息)工作:

SELECT sourceUserId, targetUserId, body,  UNIX_TIMESTAMP(time)  
FROM `usermessages`
WHERE targetUserId = 1
GROUP BY sourceUserId
ORDER BY id DESC
LIMIT 10

+----+--------------+--------------+--------+------+
| id | sourceUserId | targetUserId |  body  | time |
+----+--------------+--------------+--------+------+
| 2  |       3      |       1      | abc... | 1234 |
| 5  |       2      |       1      | abc... | 1234 |
| 8  |       4      |       1      | abc... | 1234 |
+----+--------------+--------------+--------+------+

与此相反(用户发送的消息)，请注意刚刚交换了 WHERE 和 GROUP BY:

SELECT sourceUserId, targetUserId, body,  UNIX_TIMESTAMP(time)  
FROM `usermessages`
WHERE sourceUserId = 1
GROUP BY targetUserId
ORDER BY id DESC
LIMIT 10

+----+--------------+--------------+--------+------+
| id | sourceUserId | targetUserId |  body  | time |
+----+--------------+--------------+--------+------+
| 1  |       1      |       2      | abc... | 1234 |
| 4  |       1      |       3      | abc... | 1234 |
+----+--------------+--------------+--------+------+

但如果我将这两个结果和 group by targetUserId, sourceUserId 结合起来那么它不会给出正确的结果，因为从 1 到 (2,3,4) 的所有传出消息都被分组了。

我想返回

我认为这种查询的伪代码是:

SELECT sourceUserId, targetUserId, body,  UNIX_TIMESTAMP(time)  
FROM `usermessages`
WHERE sourceUserId = 1
OR targetUserId = 1
GROUP BY (If targetUserId != 1), (If sourceUserId != 1)
ORDER BY id DESC
LIMIT 10

+----+--------------+--------------+--------+------+
| id | sourceUserId | targetUserId |  body  | time |
+----+--------------+--------------+--------+------+
| 1  |       1      |       2      | abc... | 1234 |
| 2  |       3      |       1      | abc... | 1234 |
| 8  |       4      |       1      | abc... | 1234 |
+----+--------------+--------------+--------+------+

Answer 1

版本 2这是未经测试的；它更近了，但仍然不对……虽然在这方面工作已经过时了……

SELECT Um1.sourceUserId, Um1.targetUserId, um1.body,  UNIX_TIMESTAMP(um1.time)  
FROM usermessages um1
WHERE um1.time = 
   (Select max(um1.time) 
    FROM usermessages um2 
    where um1.sourceUserID = um2.sourceUserID 
      and Um1.targetUserID = Um2.targetUserID, 
      and UM1.Body = UM2.body 
      and (targetuserID = 1 or sourceuserID = 1))
Group by Um1.sourceUserId, Um1.targetUserId, um1.body
ORDER BY id DESC
LIMIT 10

版本 1(小姐)在我看来，您还需要按 body 分组。

SELECT sourceUserId, targetUserId, body,  UNIX_TIMESTAMP(time)  
FROM `usermessages`
WHERE targetUserId = 1
OR sourceUserId = 1
GROUP BY sourceUserId, targetUserId, body,  UNIX_TIMESTAMP(time)
ORDER BY id DESC
LIMIT 10

或者使用不同的

SELECT distinct sourceUserId, targetUserId, body,  UNIX_TIMESTAMP(time)  
FROM `usermessages`
WHERE targetUserId = 1
OR sourceUserId = 1
ORDER BY id DESC
LIMIT 10

我认为问题确实在于那些记录的正文不同或时间戳不同；如果不是，那么为什么它们在表中重复？特别是在您的输出中，为什么记录 1 和 6 在那里...您是否缺少可以防止重复的唯一索引/pk？