MySQL GROUP BY/ORDER BY问题 [英] MySQL GROUP BY/ORDER BY problem

问题描述

我有一个这样的MySQL查询:

I have an MySQL query like this:

   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

虽然这可以正常工作，并且消息是按降序发送消息的用户分组的，但是现在我想对message.read行进行排序时遇到了问题.我的代码如下:

While this works fine and the messages are group by the user who sent them in DESCENDING order, I now have a problem when I want to ORDER BY the messages.read row. My code is as follows:

   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按表中的第一个分组，那么我该如何解决呢?

Now, messages.read returns the value of the row of the first message that the user send. But I need the value of the newest (highest ID) that the user send. I know that GROUP BY groups by the first in the table, so how can I get around doing this?

非常感谢您， 菲舍尔

推荐答案

我知道GROUP BY的分组由 首先在表中，那么我该如何获得 这样做吗?

I know that GROUP BY groups by the first in the table, so how can I get around doing this?

实际上并非如此.它将返回一个随机行.实际上，这通常是主键顺序的第一行，但不能保证.除MySQL以外的数据库均不允许这种做法，并且会引发错误.

That's not actually true. It will return a random row. In practice, this is often the first row in primary key order, but there is no guarantee. Databases other than MySQL do not allow this practice and will raise an error.

无论如何，检索未读消息的最高ID的一种方法是将消息表连接两次.第二个联接仅查找未读消息.例如:

Anyway, one way to retrieve the highest ID of unread messages is by joining the messages table twice. The second join only looks for unread messages. For example:

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

这篇关于MySQL GROUP BY/ORDER BY问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！