Mysql5.7版本group by 子查询中order by无效的解决办法
因为mysql 5.7版本对这个查询进行优化了,认为子查询中的order by可以进行忽略,只要Derived table里不包含如下条件就可以进行优化:
- UNION clause
- GROUP BY
- DISTINCT
- Aggregation
- LIMIT or OFFSET
能让order by生效的示例如下:
SELECT * from (SELECT * FROM table ORDER BY create_date desc limit 100000 ) t GROUP BY groupByField;
SELECT * from (SELECT * FROM table GROUP BY id ORDER BY create_date desc ) t GROUP BY groupByField;