查询分组下的最大值时子查询使用order by排序后再使用where查询导致结果有误

原文地址：https://blog.lmlyz.online/index/detail/id/65.html
mysql数据如下：

CREATE TABLE `test` (
  `mi` int(11) NOT NULL COMMENT 'mi',
  `ma` int(11) NOT NULL COMMENT 'ma ',
  `gr` varchar(1000) NOT NULL COMMENT 'gr',
  PRIMARY KEY (`mi`,`ma`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test` VALUES ('1', '2', '1');
INSERT INTO `test` VALUES ('2', '1', '2');
INSERT INTO `test` VALUES ('3', '1', '3');
INSERT INTO `test` VALUES ('4', '1', '4');
INSERT INTO `test` VALUES ('5', '1', '5');
INSERT INTO `test` VALUES ('6', '1', '6');
INSERT INTO `test` VALUES ('7', '1', '7');
INSERT INTO `test` VALUES ('8', '1', '8');
INSERT INTO `test` VALUES ('9', '3', '9');
INSERT INTO `test` VALUES ('10', '1', '10');
查询分组下的最大值
如：
SELECT
	*
FROM
	(
		SELECT
			*
		FROM
			test
		ORDER BY
			mi DESC
LIMIT 1000
	) a
WHERE ma = 1
GROUP BY ma
这样查是有问题的，得不到正确结果，可能是where打乱了里面的order排序，解决方法：
方法一：将where放进子查询里面去
SELECT
	*
FROM
	(
		SELECT
			*
		FROM
			test
		WHERE ma = 1
		ORDER BY
			mi DESC
LIMIT 1000
	) a

GROUP BY ma
方法二：使用HAVING筛选
SELECT
	*
FROM
	(
		SELECT
			*
		FROM
			test
		WHERE ma = 1
		ORDER BY
			mi DESC
LIMIT 1000
	) a

GROUP BY ma
HAVING ma = 1