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

未经允许不得转载：任鹏个人博客 » Mysql5.7先排序order by 后 group by，但是排序不生效