[mysql]ORDER BY 联接查询

标签: sql MySQL
发布时间: 2017/1/9 21:40:32
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我想取聊天组的最后一条消息,我可以对他们的排序最后更新。

一切都在我的查询中可以正常工作,除了回迁的消息是第一次创建的消息,不是最后一个。

这里是从查询示例︰

SELECT
dc.id AS chat,
dcp.*,
dcm.created AS last_message_created
FROM data_chats dc
INNER JOIN data_chats_parties dcp ON dcp.chat=dc.id
AND dcp.member=1 -- current users id
AND dcp.status >= 1
INNER JOIN data_chats_messages dcm ON dcm.chat=dc.id AND dcm.active=1
WHERE dc.active=1
GROUP BY dcp.chat 
ORDER BY dcm.created DESC

有什么我可以排序结果从 data_chats_messagescreated 列?

解决方法 1:

试穿这一件。这项工作中所有的 db

 SELECT
 dc.id AS chat,
 dcp.*,
 dcm.created AS last_message_created
 FROM data_chats dc
 INNER JOIN data_chats_parties dcp ON dcp.chat=dc.id
 AND dcp.member=1 -- current users id
 AND dcp.status >= 1
 INNER JOIN (select * from data_chats_messages order by created desc) dcm ON dcm.chat=dc.id AND dcm.active=1
 WHERE dc.active=1
 GROUP BY dcp.chat 
 ORDER BY dc.id DESC

这也会工作但不是在所有的 db。

它不知道工作在 mariadb 5.5

 select * from (SELECT
    data_chats.id AS chat,
    data_chats_parties.*,
    data_chats_messages.created AS last_message_created
  FROM data_chats
    INNER JOIN data_chats_parties
        ON data_chats_parties.chat=data_chats.id
            AND data_chats_parties.member=1 -- current users id
            AND data_chats_parties.status >= 1
    INNER JOIN data_chats_messages ON data_chats_messages.chat=data_chats.id AND data_chats_messages.active=1
  WHERE
    data_chats.active=1
  ORDER BY last_message_created DESC) a  GROUP BY chat 
官方微信
官方QQ群
31647020