php - MySQL Group By using MAX 错误结果

Question

我正在尝试获取用户每次对话的最新消息。但是，我的分组查询似乎没有带回正确的行。这是我的数据:

ID | MESSAGE | RELATED_ID | DATE_SENT
 2 | Hi      |     2      | 2013-02-21 16:03:00
 3 | Hii     |     2      | 2013-02-21 16:04:00
 4 | Hiii    |     2      | 2013-02-21 16:05:00
 5 | Hiiii   |     2      | 2013-02-21 16:06:00
 6 | Bye     |     6      | 2013-02-21 16:03:01
 7 | Byee    |     6      | 2013-02-21 16:04:01
 8 | Byeee   |     6      | 2013-02-21 16:05:01
 9 | Again   |     9      | 2013-02-21 16:03:02
 10| Againn  |     9      | 2013-02-21 16:04:02

我要找的结果是:

ID | MESSAGE | RELATED_ID | DATE_SENT
 5 | Hiiii   |     2      | 2013-02-21 16:06:00
 8 | Byeee   |     6      | 2013-02-21 16:05:01
 10| Againn  |     9      | 2013-02-21 16:04:02

我当前的查询是:

 SELECT MAX(ID), Message, Date_Sent, related_id FROM MESSAGES GROUP BY RELATED_ID LIMIT 0,100

我得到的结果是:

ID | MESSAGE | RELATED_ID | DATE_SENT
 5 | Hi      |     2      | 2013-02-21 16:03:00
 8 | Bye     |     6      | 2013-02-21 16:03:01
 10| Again   |     9      | 2013-02-21 16:03:02

它似乎获得了正确的 ID 但不是该 ID 的正确数据。

如果我能在这方面得到任何帮助，我将不胜感激。

Answer 1

您需要使用子查询为每个 related_id 选择 max(id)，然后将其加入您的表:

select m1.id,
  m1.message,
  m1.related_id,
  m1.date_sent
from messages m1
inner join
(
  select max(id) MaxId, related_id
  from messages
  group by related_id
) m2
  on m1.id = m2.MaxId
  and m1.related_id = m2.related_id
LIMIT 0,100

参见 SQL Fiddle with Demo .这样做将确保您为 SELECT 列表中的其他列返回正确的值。当您不使用 GROUP BY 或聚合 SELECT 列表中的所有项目时，MySQL 会选择其他列的值，您可能会得到意想不到的结果。 (参见 MySQL Extensions to GROUP BY)

来自 MySQL 文档:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. ... You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.