例如,假设两个用户通过直接消息交谈,您将进行消息对话。我只想从该对话中选择最新消息,然后将其显示为其消息收件箱中的对话链接......Facebook和Twitter消息是如何运作的。然后,他们可以单击上次发送的消息以查看整个对话。
包含用户之间发送的所有邮件的"我的消息"表采用以下格式:
sourceUserId 是发送消息的用户的 ID,targetUserId 是接收消息的用户的 ID,正文是消息,时间是发送消息时的时间戳。我一直保持身体作为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 行都显示完全相同的对话。
我可以通过以下查询使查询仅适用于目标用户 ID(用户收到的消息(:
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 |
+----+--------------+--------------+--------+------+
版本 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(未命中(IMO 您还需要按正文分组。
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?