mysql - 如何按 DESC 顺序分组

Question

我有下表称为问题:

ID | asker 
1  | Bob
2  | Bob
3  | Marley

我只想选择每个提问者一次，如果有多个同名提问者，请选择 ID 最高的一个。所以，预期的结果:

ID | asker 
3  | Marley
2  | Bob

我使用以下查询:

SELECT * FROM questions GROUP by questions.asker ORDER by questions.id DESC

我得到以下结果:

ID | asker 
3  | Marley
1  | Bob

它选择遇到的第一个“Bob”而不是最后一个。

Answer 1

如果您想要每个 asker 的最后一个 id，那么您应该使用聚合函数:

SELECT max(id) as id, 
   asker
FROM questions 
GROUP by asker 
ORDER by id DESC

您得到异常结果的原因是因为 MySQL 使用了 GROUP BY 的扩展，它允许选择列表中的项目是非聚合的并且不包含在 GROUP BY 子句中。然而，这可能会导致意外的结果，因为 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.

现在，如果您需要从表中返回其他列，但由于可能得到的结果不一致而不想将它们添加到 GROUP BY 中，那么您可以使用子查询来做到这一点。 (Demo)

select 
  q.Id,
  q.asker,
  q.other -- add other columns here
from questions q
inner join
(
  -- get your values from the group by
  SELECT max(id) as id, 
    asker
  FROM questions 
  GROUP by asker 
) m
  on q.id = m.id
order by q.id desc