MySQL GROUP BY/ORDER BY问题

Question

我有一个MySQL查询语句，如下：

   SELECT
         MAX(messages.ID) as maxID,
         messages.from,
         messages.read,
               users.userName
      FROM
         messages
      LEFT OUTER JOIN
         users
              ON
              users.ID = messages.from
      WHERE
       messages.to = ?
      GROUP BY
       messages.from
      ORDER BY
       maxID DESC

虽然这样做很好，而且消息按照发送者以降序分组，但是当我想要按照messages.read行进行排序时，就会出现问题。我的代码如下：

   SELECT
         MAX(messages.ID) as maxID,
         messages.from,
         messages.read,
               users.userName
      FROM
         messages
      LEFT OUTER JOIN
         users
              ON
              users.ID = messages.from
      WHERE
       messages.to = ?
      GROUP BY
       messages.from
      ORDER BY
             messages.read ASC,
       maxID DESC

现在，messages.read返回的是用户发送的第一条消息的行值。但是我需要用户发送的最新（最高ID）的值。 我知道GROUP BY按表中的第一个进行分组，那么我该如何解决这个问题呢？

非常感谢您提前的帮助， Fischer

- fischer

Answer 1

我知道GROUP BY是按照表中的第一行进行分组，那么我该如何避免这种情况呢？

事实并非如此。它会返回随机的一行数据。在实践中，通常是按照主键顺序的第一行，但不能保证。除了MySQL之外的其他数据库不允许这种做法，并会报错。

无论如何，检索未读消息中最高ID的一种方法是通过两次连接messages表来实现。第二次连接只查找未读消息。例如：

SELECT  max(msg.ID) as MaxID
,       messages.from
,       max(unread.ID) as MaxUnreadID
,       users.userName
FROM    users
join    messages msg
on      users.ID = msg.from
        and messages.to = ?
left join    
        messages unread
on      users.ID = unread.from
        and messages.to = ?
        and unread.read = 0
GROUP BY
        users.ID
,       users.userName

- Andomar

---

谢谢你的回答。我不知道MySQL在GROUP BY时会返回随机行 :) 但是，如果未读，则messages.read为0，如果已读，则为1。所以我不认为我可以从你的建议中实现我想要的！ - fischer

@fischer：没错，回答已经编辑了一种检索最高未读ID的方法。 - Andomar

Answer 2

我并不完全理解这个 SQL 的含义，但你可以在主查询中使用没有分组的子查询，并在之后进行分组。你可以试一下这种方法：

SELECT 
    *
FROM
    (
        SELECT
            MAX(messages.ID) as maxID,
            messages.from,
            messages.read,
            users.userName
        FROM
            messages
        LEFT OUTER JOIN
            users
            ON
            users.ID = messages.from
        WHERE
            messages.to = ?
        ORDER BY
            messages.read ASC,
            maxID DESC
    ) AS msg
GROUP BY
   msg.from

- Wowa