MySQL查询：GroupBy后取出每一组中最大的数据 [数据库教程]

Z时代
2024-01-10
分类：综合

database

环境：MySQL5.7版本

先生成一组测试数据

publicstaticint randAge(){
returnnew Random().nextInt(100);
}
publicstaticchar randScore(){
int i = new Random().nextInt(4) + 65;
return (char)i;
}
publicstatic String randName(){
    StringBuilder s = new StringBuilder();
for (int i = 0; i < 3; i++){
        s.append((char) (0x4e00 + (int) (Math.random() * (0x9fa5 - 0x4e00 + 1))));
    }
return s.toString();
}
publicstaticvoid main(String[] args) {
for(int i = 1; i <= 20; i++){
        System.out.println("INSERT INTO `student`(`id`, `name`, `age`, `score`) VALUES ("+ i +", ‘"+ randName() +"‘, "+ randAge() +", ‘"+  randScore() +"‘);");
    }
}

比如：

INSERT INTO `student`(`id`, `name`, `age`, `score`) VALUES (1, ‘襠醞奪‘, 70, ‘C‘);
INSERT INTO `student`(`id`, `name`, `age`, `score`) VALUES (2, ‘穟玔檠‘, 77, ‘C‘);
INSERT INTO `student`(`id`, `name`, `age`, `score`) VALUES (3, ‘繜翿宽‘, 11, ‘A‘);
INSERT INTO `student`(`id`, `name`, `age`, `score`) VALUES (4, ‘敕搢謝‘, 55, ‘A‘);
INSERT INTO `student`(`id`, `name`, `age`, `score`) VALUES (5, ‘薰舒翎‘, 86, ‘D‘);
INSERT INTO `student`(`id`, `name`, `age`, `score`) VALUES (6, ‘贿怆軻‘, 31, ‘B‘);
INSERT INTO `student`(`id`, `name`, `age`, `score`) VALUES (7, ‘踰齁陈‘, 26, ‘D‘);
INSERT INTO `student`(`id`, `name`, `age`, `score`) VALUES (8, ‘溧况癈‘, 94, ‘B‘);
INSERT INTO `student`(`id`, `name`, `age`, `score`) VALUES (9, ‘捳縇睢‘, 38, ‘D‘);
INSERT INTO `student`(`id`, `name`, `age`, `score`) VALUES (10, ‘漲堔杙‘, 0, ‘C‘);
INSERT INTO `student`(`id`, `name`, `age`, `score`) VALUES (11, ‘輪塶讣‘, 13, ‘B‘);
INSERT INTO `student`(`id`, `name`, `age`, `score`) VALUES (12, ‘刪庝熽‘, 53, ‘B‘);
INSERT INTO `student`(`id`, `name`, `age`, `score`) VALUES (13, ‘鄀乹聐‘, 90, ‘D‘);
INSERT INTO `student`(`id`, `name`, `age`, `score`) VALUES (14, ‘婈谂畛‘, 52, ‘B‘);
INSERT INTO `student`(`id`, `name`, `age`, `score`) VALUES (15, ‘甔鼖苫‘, 96, ‘D‘);
INSERT INTO `student`(`id`, `name`, `age`, `score`) VALUES (16, ‘刘卟卅‘, 55, ‘A‘);
INSERT INTO `student`(`id`, `name`, `age`, `score`) VALUES (17, ‘够負饶‘, 3, ‘D‘);
INSERT INTO `student`(`id`, `name`, `age`, `score`) VALUES (18, ‘瀠禮翍‘, 14, ‘A‘);
INSERT INTO `student`(`id`, `name`, `age`, `score`) VALUES (19, ‘仉余淳‘, 5, ‘C‘);
INSERT INTO `student`(`id`, `name`, `age`, `score`) VALUES (20, ‘坛唧笶‘, 59, ‘C‘);

思路

1. 先来个最简单的分组，找出每一组最大的

mysql>select score,max(age) age from student groupby score;
+-------+-----+
| score | age |
+-------+-----+
| A     |55|
| B     |94|
| C     |77|
| D     |96|
+-------+-----+
4 rows inset (0.03 sec)

但是这样无法得知id和name。

2. 来个左连接查询

mysql>select a.id, a.name, a.age, a.score, b.age, b.score from student a leftjoin (select score,max(age) age from student groupby score) b on a.score = b.score and a.age = b.age;
+----+--------+-----+-------+------+-------+
| id | name   | age | score | age  | score |
+----+--------+-----+-------+------+-------+
|4| 敕搢謝 |55| A     |55| A     |
|16| 刘卟卅 |55| A     |55| A     |
|8| 溧况癈 |94| B     |94| B     |
|2| 穟玔檠 |77| C     |77| C     |
|15| 甔鼖苫 |96| D     |96| D     |
|1| 襠醞奪 |70| C     |NULL|NULL|
|3| 繜翿宽 |11| A     |NULL|NULL|
|5| 薰舒翎 |86| D     |NULL|NULL|
|6| 贿怆軻 |31| B     |NULL|NULL|
|7| 踰齁陈 |26| D     |NULL|NULL|
|9| 捳縇睢 |38| D     |NULL|NULL|
|10| 漲堔杙 |0| C     |NULL|NULL|
|11| 輪塶讣 |13| B     |NULL|NULL|
|12| 刪庝熽 |53| B     |NULL|NULL|
|13| 鄀乹聐 |90| D     |NULL|NULL|
|14| 婈谂畛 |52| B     |NULL|NULL|
|17| 够負饶 |3| D     |NULL|NULL|
|18| 瀠禮翍 |14| A     |NULL|NULL|
|19| 仉余淳 |5| C     |NULL|NULL|
|20| 坛唧笶 |59| C     |NULL|NULL|
+----+--------+-----+-------+------+-------+
20 rows inset (0.08 sec)

左面的是原数据表的内容，右面多出来的两行是我们分组统计之后的数据。

3. 两个查询调换位置或者用右连接查询，再加个排序

mysql>select a.id, a.name, b.age, b.score from student a rightjoin (select score,max(age) age from student groupby score) b on a.score = b.score and a.age = b.age orderby b.score;
+----+--------+-----+-------+
| id | name   | age | score |
+----+--------+-----+-------+
|4| 敕搢謝 |55| A     |
|16| 刘卟卅 |55| A     |
|8| 溧况癈 |94| B     |
|2| 穟玔檠 |77| C     |
|15| 甔鼖苫 |96| D     |
+----+--------+-----+-------+
5 rows inset (0.07 sec)