SQL查询:使用SQL连接对最新消息进行分组

SELECT
       MAX(sequence) AS latestmsg,
       COUNT(*) AS msgcount,
       sequence,
       messageid,
       users.username,
       receipient,
       message,
       datestamp 
    FROM
       messages 
       JOIN
          users 
          ON messages.username = users.username 
       JOIN
          posts 
          ON posts.postid = messages.messageid 
    WHERE
       messages.receipient = 'try-2a' 
    GROUP BY
       messages.messageid 
    ORDER BY
       messages.sequence DESC

尝试以下修改的查询:

SELECT sequence AS latestmsg,
    msgcount,
    sequence,
    messages.messageid,
    users.username,
    receipient,
    message,
    messages.datestamp 
FROM messages 
JOIN users ON messages.username = users.username 
JOIN posts ON posts.postid = messages.messageid 
JOIN (
    SELECT messageid, MAX(sequence) AS max_seq, COUNT(*) AS msgcount
    FROM messages
    GROUP BY messageid
) m ON m.messageid = messages.messageid AND m.max_seq = messages.sequence
WHERE messages.receipient = 'try-2a' 
ORDER BY messages.sequence DESC

这个 username 中的列 SELECT 列表需要加上表别名的前缀,因为 用户名 两者都存在 messages 和 users

SELECT
   MAX(sequence) AS latestmsg,
   COUNT(*) AS msgcount,
   sequence, 
   messageid,
   username, --This column needs to have the alias prefix
   receipient,
   message,
   datestamp 

如果您试图查找每个用户的最新消息,则需要按用户分组,然后查找每个用户组中每个消息的最大时间戳。您可以使用它来选择时间戳与其匹配的消息。