MySQL5.7排序后GROUP BY

MySQL5.7版本中GROUP BY，获取最大值问题

 

新建一个数据表并插入数据

CREATE TABLE `a` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `point` int(11) DEFAULT NULL,
  `class` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

INSERT INTO `a` (`id`, `point`, `class`) VALUES ('1', '88', '1');
INSERT INTO `a` (`id`, `point`, `class`) VALUES ('2', '89', '1');
INSERT INTO `a` (`id`, `point`, `class`) VALUES ('3', '99', '1');
INSERT INTO `a` (`id`, `point`, `class`) VALUES ('4', '77', '2');
INSERT INTO `a` (`id`, `point`, `class`) VALUES ('5', '86', '2');
INSERT INTO `a` (`id`, `point`, `class`) VALUES ('6', '53', '2');
INSERT INTO `a` (`id`, `point`, `class`) VALUES ('7', '69', '3');
INSERT INTO `a` (`id`, `point`, `class`) VALUES ('8', '98', '3');
INSERT INTO `a` (`id`, `point`, `class`) VALUES ('9', '13', '3');
INSERT INTO `a` (`id`, `point`, `class`) VALUES ('10', '77', '1');
INSERT INTO `a` (`id`, `point`, `class`) VALUES ('11', '87', '1');
INSERT INTO `a` (`id`, `point`, `class`) VALUES ('12', '86', '2');
INSERT INTO `a` (`id`, `point`, `class`) VALUES ('13', '54', '2');
INSERT INTO `a` (`id`, `point`, `class`) VALUES ('14', '76', '3');
INSERT INTO `a` (`id`, `point`, `class`) VALUES ('15', '99', '3');

 

 

SELECT * FROM a ORDER BY point DESC

point字段会根据排序显示最大值；

SELECT * FROM ( SELECT * FROM a ORDER BY point DESC ) as t GROUP BY t.class

加入子查询中后point字段就的不是最大值了；

SELECT * FROM ( SELECT * FROM a ORDER BY point DESC limit 10000000000) as t GROUP BY t.class

通过 explain 查看执行计划，可以看到没有 limit 的时候，少了一个 DERIVED 操作
估计是内部优化了，认为 ORDER BY 在这种语法中可忽略
有 LIMIT 限制涉及排序后的结果，不会忽略 ORDER BY，可以达到预期