mysql - 从所有对话中选择最后一条消息(MySQL)
问题描述
我需要为具有给定 ID 的用户选择每个对话的所有最后消息。
如果最后一条消息被发送到给定的 id,它必须是来自发件人的最后一条消息。
这是没有使用 messageID 的 creationDate 的测试用例:
+-----------+------------+----------+------+
| messageID | fromUserID | toUserID | text |
+-----------+------------+----------+------+
| 1 | 1 | 2 | 'aa' |
| 2 | 1 | 3 | 'ab' |
| 3 | 2 | 1 | 'ac' |
| 4 | 2 | 1 | 'ad' |
| 5 | 3 | 2 | 'ae' |
+-----------+------------+----------+------+
userID=1 的结果必须是带有文本“ab”和“ad”的消息。
现在,我有这个查询,其中包含每个用户彼此的所有最后消息,但根据我的测试用例,没有删除 id=1 的消息(必须只有 id=2 和 id=4)。
SELECT
UM.messageID,
UM.fromUserID, UM.toUserID,
UM.text, UM.flags, UM.creationDate
FROM UserMessage AS UM
INNER JOIN
(
SELECT
MAX(messageID) AS maxMessageID
FROM UserMessage
GROUP BY fromUserID, toUserID
) IUM
ON UM.messageID = IUM.maxMessageID
WHERE UM.fromUserID = 1 OR UM.toUserID = 1
ORDER BY UM.messageID DESC
解决方案
一个简单的方法是
select um.*
from usermessage um
where um.messageid = (select min(um2.messageid)
from usermessage um2
where (um2.fromuserid, touserid) in ( (um.fromuserid, um.touserid), (um.touserid, um.fromuserid) )
);
或者,在 MySQL 8+ 中:
select um.*
from (select um.*,
row_number() over (partition by least(um.fromuserid, um.touserid), greatest(um.fromuserid, um.touserid) order by um.messageid desc) as seqnum
from usermessage um
) um
where seqnum = 1;
推荐阅读
- javascript - 使用javascript的ajax中未定义的索引错误
- python - 为什么我的列表没有更新,尽管附加到列表中?
- google-cloud-platform - 站点停机/服务器成本是应有的 10 倍/缺少 VM 实例 - Google Cloud Platform
- jinja2 - jupyter nbconvert / jinja2 在哪里寻找模板?
- javascript - 如何使用一组 {x,y} 对象来制作图表?
- firebase - Firestore 数据不是来自打开多个选项卡、启用持久性和 synchronizeTabs 的缓存:true
- node.js - 我想从 SQLite 数据库中选择一个值并将其保存在一个变量中
- c# - 如何在一对一映射上配置 Fluent Compound ForeignKey
- c# - ASP.NET Core JSON 参数始终为空
- python - 比较 CSV 并使用 Python 从源和目标打印出不同的行