MySQL查询：Group By 后取出每一组中最大的数据

时间：2024-03-08 08:39:16

环境：MySQL5.7版本

先生成一组测试数据

public static int randAge(){
    return new Random().nextInt(100);
}
public static char randScore(){
    int i = new Random().nextInt(4) + 65;
    return (char)i;
}
public static 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();
}
public static void 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 group by score;
+-------+-----+
| score | age |
+-------+-----+
| A     |  55 |
| B     |  94 |
| C     |  77 |
| D     |  96 |
+-------+-----+
4 rows in set (0.03 sec)

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

2. 来个左连接查询

mysql> select a.id, a.name, a.age, a.score, b.age, b.score from student a left join (select score,max(age) age from student group by 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 in set (0.08 sec)

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

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

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

 

相关文章