如何按 DESC 顺序分组
我有下表称为问题:
ID | asker
1 | Bob
2 | Bob
3 | Marley
我只想选择每个提问者一次，如果有多个同名提问者，请选择 ID 最高的一个.所以，预期的结果:
I want to select each asker only once and if there are multiple askers with the same name, select the one of the highest id. So, the expected results:
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"而不是最后一个.
It selects the first 'Bob' it encounters instead of the last one.
推荐答案
如果你想要每个asker的最后一个id，那么你应该使用聚合函数:
If you want the last id for each asker, then you should use an aggregate function:
SELECT max(id) as id,
asker
FROM questions
GROUP by asker
ORDER by id DESC
你得到不寻常结果的原因是因为 MySQL 使用了 GROUP BY 的扩展，它允许选择列表中的项目是非聚合的，而不是包含在 GROUP BY 子句中.然而，这会导致意外的结果，因为 MySQL 可以选择返回的值.(参见 MySQL 对 GROUP BY 的扩展)
The reason why you were getting the unusual result is because MySQL uses an extension to GROUP BY which allows items in a select list to be nonaggregated and not included in the GROUP BY clause. This however can lead to unexpected results because MySQL can choose the values that are returned. (See MySQL Extensions to GROUP BY)
来自 MySQL 文档:
From the MySQL Docs:
MySQL 扩展了 GROUP BY 的使用，以便选择列表可以引用未在 GROUP BY 子句中命名的非聚合列....您可以使用此功能通过避免不必要的列排序和分组来获得更好的性能.但是，这主要在未在 GROUP BY 中命名的每个非聚合列中的所有值对于每个组都相同时很有用.服务器可以自由地从每个组中选择任何值，因此除非它们相同，否则选择的值是不确定的.此外，添加 ORDER BY 子句不会影响从每个组中选择值.结果集的排序发生在选择值之后，ORDER BY 不影响服务器选择哪些值.
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，那么您可以使用子查询来做到这一点.(演示)
Now if you had other columns that you need to return from the table, but don't want to add them to the GROUP BY due to the inconsistent results that you could get, then you could use a subquery to do so. (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
相关文章