SQL 对话/类似聊天​​的输出 [英] SQL conversation/chat-like output

问题描述

我正在尝试制作一个类似对话的 MySQL 表 I 输出，如下所示:

I'm trying to make a conversation-like output of a MySQL table I which looks like this:

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

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

And I want it to output like a conversation, so only the last message between two users will be shown (order by a date). So the user with ID 36 will see this above table like:

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

因此不会显示Lorem"，因为 Ipsum 是相同用户之间的较新消息.

So "Lorem" won't be shown because Ipsum is a newer message between the same users.

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

I now use this Query, but this won't output the latest message if this is send by the user itself:

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

希望有人能帮助我，在此先感谢！

I hope someone can help me, thanks in advance!

推荐答案

知道现在回答可能为时已晚，但以防万一这对某些人有所帮助1 - 下面的查询正是 OP 所要求的 - 4 个选择和 2 个连接.

know it may be to late to answer, but just in case this helps some1 - the below query does just what is requested by OP - 4 selects and 2 joins.

$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";

这篇关于SQL 对话/类似聊天​​的输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！