gpt4 book ai didi

mysql - SQL 对话/类似聊天​​的输出

转载 作者:行者123 更新时间:2023-11-29 23:09:09 25 4
gpt4 key购买 nike

我正在尝试对 MySQL 表 I 进行类似对话的输出,如下所示:

Content           From           To           Date
Lorem 36 3 10-10-2014
Ipsum 3 36 10-12-2014
Dolor 36 4 11-10-2014

我希望它像对话一样输出,因此只会显示两个用户之间的最后一条消息(按日期排序)。因此 ID 为 36 的用户将看到上表,如下所示:

Content          From            To           Date           
Ipsum 3 36 10-12-2014
Dolor 36 4 11-10-2014

因此“Lorem”不会显示,因为 Ipsum 是同一用户之间的较新消息。

编辑:

我现在使用此查询,但如果这是由用户自己发送的,则不会输出最新消息:

SELECT content, sent
FROM personal_messages pm
INNER JOIN (
SELECT MAX( id ) id, from_user, to_user
FROM personal_messages
WHERE from_user = 46 OR to_user = 46
GROUP BY to_user, from_user
) pm2 ON pm.id = pm2.id
INNER JOIN users u ON pm.from_user = u.id
ORDER BY pm.sent

希望有人能帮助我,先谢谢了!

最佳答案

知道回答可能已经太晚了,但以防万一这对某些人有帮助1 - 下面的查询正是 OP 所要求的 - 4 个选择和 2 个连接。

$sql="
SELECT T2.maxDate, T2.user2_id, T2.ava, T2.userName,chat.user_to,chat.user_from,chat.body,chat.viewed FROM
(SELECT T1.user2_id, users.userName, users.ava, max(cdate) maxDate FROM
(SELECT chat.user_to user2_id, max(msg_time) cdate
FROM chat WHERE chat.user_from=18
GROUP BY chat.user_to
union distinct
(SELECT chat.user_from user2_id, max(msg_time) cdate
FROM chat WHERE chat.user_to=18
GROUP BY chat.user_from)) T1
inner join users on (users.userID = T1.user2_id)
group by T1.user2_id
order by maxDate desc) T2
join chat on (T2.maxDate = chat.msg_time) ORDER BY T2.maxDate DESC";

关于mysql - SQL 对话/类似聊天​​的输出,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/28152702/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com