MySql分组后显示指定字段的最大或最小值所在的记录

我们有时候会有这种需求:查询分组后的结果作为子表,而主表需要子表中每组的某列的最大或最小值.但是如果在 GROUP BY 后添加 ORDER BY 只是对分组后的数据进行排序,影响不到分组里面.针对这种需求我们有两中方法:
1.通过ROLLUP.
2.通过自连查询.
首先我们新建一些测试数据:

------------------------------------------------------------------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `grade` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

INSERT INTO `test` VALUES ('1', '张三', '18', '1');
INSERT INTO `test` VALUES ('2', '李四', '19', '1');
INSERT INTO `test` VALUES ('3', '王五', '20', '1');
INSERT INTO `test` VALUES ('4', '赵六', '18', '1');
INSERT INTO `test` VALUES ('5', '洪七公', '20', '2');
INSERT INTO `test` VALUES ('6', '柯镇恶', '19', '2');
INSERT INTO `test` VALUES ('7', '乔峰', '18', '2');
INSERT INTO `test` VALUES ('8', '钢铁侠', '21', '3');
INSERT INTO `test` VALUES ('9', '蜘蛛侠', '20', '3');
INSERT INTO `test` VALUES ('10', '绿巨人', '18', '3');
------------------------------------------------------------------------------------

-- GROUP BY 和 ORDER BY 的方式
SELECT *,count(*) FROM test GROUP BY grade ORDER by age DESC

我们可以看到这种方式是得不到想要的数据的, 每个年级没有显示年龄最大的人.

-- ROLLUP的方式
SELECT *, max(age), count(*) FROM test GROUP BY grade WITH ROLLUP

我们可以看到这种方式是得到了每个年级最大年龄, 但所显示的记录并不全有可能达不到我们的需求.

-- 自连查询的方式
SELECT *, (SELECT COUNT(DISTINCT test.age) FROM test WHERE grade = t.grade) count
FROM test t
WHERE NOT EXISTS (SELECT 1 FROM test WHERE grade = t.grade AND age > t.age)

我们可以看到这种方式是把最大年龄所对应的记录显示出来, 达到了需求并且可以根据不同的状况改变我们的条件.