mysql - JOIN 查询的 ORDER BY

Question

我正在尝试获取一组聊天的最后一条消息，以便我可以按上次更新对它们进行排序。

我的查询中的所有内容都工作正常，除了获取的消息是第一个创建的消息，而不是最后一个。

以下是查询示例:

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

我是否可以按 created 列对 data_chats_messages 的结果进行排序？

Answer 1

试试这个。这在所有数据库中都有效

 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

This will also work but not in all db .

it is not known to work in 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