jquery - 获取聊天系统的最后一条消息

Question

我的问题是我正在尝试创建一个聊天消息系统，

这就是我目前所拥有的。

Chat
chat_id | member_id | added

Member to Chat
member_to_chat_id | member_id | chat_id

Chat Message
chat_message_id | member_to_chat_id | message | added

所以我想获取所有涉及成员的聊天记录。我是这样做的

Select c.chat_id 
From member_to_chat m2c 
Left Join chat c ON c.chat_id = m2c.chat_id WHERE m2c.member_id = 1 
GROUP by c.chat_id

fiddle 在这里 http://sqlfiddle.com/#!9/4d1ab/1

有没有办法获取此群聊中的最后一条消息并形成它的成员是谁？

预期的结果应该是这样的

如果用户 Alex 在两个聊天中，并且他写了最后一条消息

chat_id | member_name | member_message
1         Alex          Test1
2         Alex          Test2

稍后，如果其他人在 ID 为 1 的聊天中添加了一条消息，我们仍然会通过用户 ID 1:Alex 获得所有聊天，但该消息应该来自另一个成员，所以类似这样:

chat_id | member_name | member_message
1         John          Last Test1
2         Alex          Test2

希望这现在有意义。

Answer 1

好的，我希望这是我要进行的最后一次编辑这里是你如何可以做你想做的事情我一开始并不完全理解你的问题但是你最后一次编辑我认为这是解决方案:

SELECT * FROM(
    SELECT m2c.chat_id, m.name, cm.message
    FROM member_to_chat m2c
    INNER JOIN chat_message cm
    ON m2c.member_to_chat_id = cm.member_to_chat_id
    INNER JOIN members m
    ON m2c.member_id = m.member_id
    WHERE m2c.chat_id IN (SELECT c.chat_id
                          FROM member_to_chat m2c 
                          INNER JOIN chat c ON c.chat_id = m2c.chat_id 
                          WHERE m2c.member_id = 2)
    ORDER BY cm.chat_message_id DESC) resultTable
GROUP BY chat_id

这是 fiddle http://sqlfiddle.com/#!9/d7443/9/0测试它(这是你的 fiddle ，只是为了测试查询而稍加编辑)GL

编辑:

再次嗨，我不得不再次改进我的答案。感谢 Strawberry 提供的提示和建议，我找到了解决此问题的更好方法...(您可以在该答案下方的评论中看到我们的小讨论)...

这是手册的链接，一切从这里开始 https://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html

首先是相关子查询的代码:

SELECT m2c.chat_id, m.name, cm.message
FROM member_to_chat m2c
INNER JOIN chat_message cm
ON m2c.member_to_chat_id = cm.member_to_chat_id
INNER JOIN members m
ON m2c.member_id = m.member_id
WHERE m2c.chat_id IN (SELECT c.chat_id
                      FROM member_to_chat m2c 
                      INNER JOIN chat c ON c.chat_id = m2c.chat_id 
                      WHERE m2c.member_id = 2)
AND cm.chat_message_id = (SELECT MAX(cmm.chat_message_id)
                          FROM chat_message cmm
                          INNER JOIN member_to_chat m2cm
                          on m2cm.member_to_chat_id = cmm.member_to_chat_id
                          WHERE m2c.chat_id = m2cm.chat_id)

这里是 SQL Fiddle:http://sqlfiddle.com/#!9/d7443/17/0

这是基于 JOIN 的第二种解决方案的代码:

SELECT t1.chat_id, name, message FROM (
SELECT m2c.chat_id, m.name, cm.message, cm.chat_message_id
FROM member_to_chat m2c
INNER JOIN chat_message cm
ON m2c.member_to_chat_id = cm.member_to_chat_id
INNER JOIN members m
ON m2c.member_id = m.member_id
WHERE m2c.chat_id IN (SELECT c.chat_id
                      FROM member_to_chat m2c 
                      INNER JOIN chat c ON c.chat_id = m2c.chat_id 
                      WHERE m2c.member_id = 2)) AS t1                 
JOIN (SELECT m2c.chat_id, MAX(cm.chat_message_id) as maxChatId
      FROM chat_message cm
      INNER JOIN member_to_chat m2c
      ON m2c.member_to_chat_id = cm.member_to_chat_id
      GROUP BY chat_id) AS t2 
ON t1.chat_id = t2.chat_id 
AND t1.chat_message_id = t2.maxChatId

这是一个 fiddle :http://sqlfiddle.com/#!9/d7443/43/0

此答案的改进再次归功于 Strawberry，他向我展示了解决此类问题的更好方法。再次感谢!

附言抱歉，更新晚了，我还有其他想法要做，但总比没有好。 :) GL