php – 如何按照DESC顺序进行分组

发布时间：2020-12-13 16:43:15 所属栏目：PHP教程来源：网络整理

我有下面的问题：

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

我想选择每个asker只有一次,如果有多个同名的问题,选择最高的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”,而不是最后一个.

谢谢

如果你想要每个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.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!