mysql - 获取每个朋友的所有最后一条消息 - SQL
问题描述
我正在尝试从每个朋友那里选择最后一条消息,就像所有其他聊天应用程序一样。这是我到目前为止所拥有的。
SELECT m.message,
m.message_read,
m.message_date,
CASE WHEN m.sender = 4
THEN m.receiver
ELSE m.sender
END as friend_id,
CASE WHEN m.sender = 4
THEN p2.nickname
ELSE p1.nickname
END as name,
CASE WHEN m.sender = 4
THEN p2.image
ELSE p1.image
END as image
FROM message as m
JOIN profile as p1
ON m.sender = p1.user_id -- sender
JOIN profile as p2
ON m.receiver = p2.user_id -- receiver
WHERE 4 IN (m.sender, m.receiver)
上面的选择语句得到
+-----------+--------------+---------------------+-----------+-------+-------+
| message | message_read | message_date | friend_id | name | image |
+-----------+--------------+---------------------+-----------+-------+-------+
| Hey Buddy | 1 | 2018-05-10 11:58:39 | 1 | JUAN | NULL |
| SUP MATE | 1 | 2018-05-15 11:04:24 | 1 | JUAN | NULL |
| nooo | 1 | 2018-05-15 10:24:36 | 2 | user3 | NULL |
| lulz | 1 | 2018-05-15 10:24:36 | 2 | user3 | NULL |
| shut up | 1 | 2018-05-15 10:24:36 | 2 | user3 | NULL |
| heha | 1 | 2018-05-15 10:36:11 | 2 | user3 | NULL |
+-----------+--------------+---------------------+-----------+-------+-------+
我专门使用消息表中的 message_date 变量来整理这些消息。
留言表:
+----+--------+----------+-----------+--------------+-----------------+---------------------+
| id | sender | receiver | message | message_read | message_visible | message_date |
+----+--------+----------+-----------+--------------+-----------------+---------------------+
| 1 | 4 | 2 | lulz | 1 | 2 | 2018-05-15 10:24:36 |
| 2 | 1 | 4 | Hey Buddy | 1 | NULL | 2018-05-10 11:58:39 |
| 3 | 2 | 4 | nooo | 1 | 2 | 2018-05-15 10:24:36 |
| 4 | 4 | 2 | shut up | 1 | 2 | 2018-05-15 10:24:36 |
| 5 | 4 | 2 | heha | 1 | NULL | 2018-05-15 10:36:11 |
| 6 | 1 | 4 | SUP MATE | 1 | NULL | 2018-05-15 11:04:24 |
+----+--------+----------+-----------+--------------+-----------------+---------------------+
我想得到结果
+-----------+--------------+---------------------+-----------+-------+-------+
| message | message_read | message_date | friend_id | name | image |
+-----------+--------------+---------------------+-----------+-------+-------+
| SUP MATE | 1 | 2018-05-15 11:04:24 | 1 | JUAN | NULL |
| heha | 1 | 2018-05-15 10:36:11 | 2 | user3 | NULL |
+-----------+--------------+---------------------+-----------+-------+-------+
请帮我解决这个问题。任何评论都会有所帮助!这也应该考虑相同的 message_date。例如,如果有两条消息具有完全相同的日期/时间,则只应在其中选择一条。我正在考虑消息表的 id 以选择其中的哪一个。
解决方案
有点像
select m.*
from
message m
left join message m1 on (
(
(m.sender = m1.sender and m.receiver = m1.receiver)
or
(m.sender = m1.receiver and m.receiver = m1.sender )
)
and case when m.message_date = m1.message_date
then m.id < m1.id
else m.message_date < m1.message_date
end
)
where m1.id is null
and 4 in(m.sender, m.receiver)
我添加了case
表达式来检查同一日期时间是否有超过 1 条消息,然后根据 id 选择最新消息,我假设 id 设置为自动递增。
推荐阅读
- javascript - Javascript动画剪辑路径鼠标坐标
- r - 将括号添加到具有给定条件的字符串
- continuous-integration - Gitlab CI:仅在工件存在时运行作业
- postgresql - 使用类型字段数字在postgres中不精确的数学?
- design-patterns - 单例模式 VS 包加载
- c# - .NET - 从控制台与工作人员交互的正确方法是什么?
- chef-infra - 没有属性时的 load_current_value
- reactjs - useQuery 中的 Apollo 状态变量
- php - 尝试使用 MINI PHP MVC 制作搜索栏
- python - 使用 Python 评估包含命名空间的 xml 的 xpath