网站首页技术博客
Mysql5.7先排序order by 后 group by,但是排序不生效
摘要经常会遇到需要对数据先排序后分组的情况,正常情况下执行下面的SQL便可以
SELECT * from (SELECT * FROM `jr_interview_operation` WHERE ( `interview_id` = 26 AND `type` = 3 ) AND `jr_interview_operation`.`delete_time` = 0 ORDER BY `id`
经常会遇到需要对数据先排序后分组的情况,正常情况下执行下面的SQL便可以
SELECT * from (SELECT * FROM `jr_interview_operation` WHERE ( `interview_id` = 26 AND `type` = 3 ) AND `jr_interview_operation`.`delete_time` = 0 ORDER BY `id` desc ) a group by a.person_id
但是实际使用时,子查询中的排序并没有生效,这是由于mysql5.7以后优化掉了子查询中的排序操作。
需要使用子查询中的排序操作的话加上limit语句就可以
SELECT * from (SELECT * FROM `jr_interview_operation` WHERE ( `interview_id` = 26 AND `type` = 3 ) AND `jr_interview_operation`.`delete_time` = 0 ORDER BY `id` desc limit 0,1000 ) a group by a.person_id
explain 查看执行计划,发现在没有 limit 的情况,会少了一个derived 操作
没有limit的情况