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

环境：MySQL5.7版本

先生成一组测试数据

public static intrandAge(){return new Random().nextInt(100);

}public static charrandScore(){int i = new Random().nextInt(4) + 65;return (char)i;

}public staticString randName(){

StringBuilder s= newStringBuilder();for (int i = 0; i < 3; i++){

s.append((char) (0x4e00 + (int) (Math.random() * (0x9fa5 - 0x4e00 + 1))));

}returns.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 byscore;+-------+-----+

| 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 byb.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)

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