mysql - 如何使这个 mysql 查询显示不同的行？

Question

SELECT DISTINCT message_group.group_id, messages.*
FROM messages
LEFT JOIN message_group ON message_group.group_id = messages.group_id
WHERE message_group.username = 'admin'
OR message_group.recipients = 'admin'
ORDER BY messages.id DESC

以下是它显示数据的方式，即使我指定了DISTINCT message_group.group_id

为什么要这样做？我怎样才能让它只显示一个组ID？

更多引用:https://stackoverflow.com/questions/36167801/how-to-order-message-groups-messages-just-like-imessage

Answer 1

试试这个:

SELECT message_group.group_id, messages.*
FROM messages INNER JOIN
    (SELECT 
        MAX(`timestamp`) AS latest,
        group_id
     FROM messages
     GROUP BY group_id) m2 
     ON messages.group_id = m2.group_id AND messages.`timestamp` = m2.`timestamp`
LEFT JOIN message_group ON message_group.group_id = messages.group_id
WHERE message_group.username = 'admin'
OR message_group.recipients = 'admin'
ORDER BY messages.id DESC