Mysql 5.7 group by 组内排序无效的解决方法

数据库版本 mysql 5.7.21

执行语句:

SELECT  *,count( id ) AS id 
FROM
    ( SELECT * FROM message ORDER BY created_date DESC) tt 
GROUP BY
    conversation_id 
ORDER BY
    created_date DESC;

发现组内排序无效。

但如果在子查询中加入 limit 就能实现组内排序

SELECT  *,count( id ) AS id 
FROM
    ( SELECT * FROM message ORDER BY created_date DESC LIMIT 0, 100 ) tt 
GROUP BY
    conversation_id 
ORDER BY
    created_date DESC;

原因:5.7以后对排序的sql解析做了优化,子查询中的排序

你可能感兴趣的