网站首页技术博客

Mysql5.7先排序order by 后 group by,但是排序不生效

洞天水月2021-05-20 12:07:50147人次阅读
摘要经常会遇到需要对数据先排序后分组的情况,正常情况下执行下面的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的情况

文章评论