Mysql5.7版本group by 子查询中order by无效的解决办法
因为mysql 5.7版本对这个查询进行优化了,认为子查询中的order by可以进行忽略,只要Derived table里不包含如下条件就可以进行优化:
UNION clause
GROUP BY
DISTINCT
Aggregation
LIMIT or OFFSET
能让order by生效的示例如下:
1|SELECT * from (SELECT * FROM table ORDER BY create_date desc limit 100000 ) t GROUP BY groupByField;
2|SELECT * from (SELECT * FROM table GROUP BY id ORDER BY create_date desc ) t GROUP BY groupByField;
MySQL · 新特性分析 · 5.7中Derived table变形记
原文链接:https://blog.csdn.net/sinat_30228861/article/details/111176501