mysql order by 错误_MySQL的GROUP BY和ORDER BY错误结果

这是您的查询：

SELECT *

FROM (SELECT *

FROM user_comission_configuration_history

ORDER BY on_date DESC

) AS ordered_history

WHERE user_id = 408002

GROUP BY comission_id;

查询的一个主要问题是它使用MySQL扩展来对MySQL明确警告的分组依据.扩展是使用select中的其他列,这些列不在by函数或聚合函数中.警告(here)为：

MySQL extends the use of GROUP BY so that the select list can refer to

nonaggregated columns not named in the GROUP BY clause. This means

that the preceding query is legal in MySQL. 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.

因此,列中返回的值是不确定的.

这是一种获取所需内容的非常有效的方法(使用英文正确拼写的“佣金”)：

SELECT *

FROM user_commission_configuration_history cch

WHERE NOT EXISTS (select 1

from user_commission_configuration_history cch2

where cch2.user_id = cch.user_id and

cch2.commission_id = cch.commission_id and

cch2.on_date > cch.on_date

) AND

cch.user_id = 408002;