MySQL groups first and then takes the largest and smallest records in the group

需求：对MySQLAll records are grouped,Take the maximum value of a field in the group/最小值

解决办法：

1.Sort first and then group to get the first record after sorting（Applies to duplicate records）

环境准备：mysql5.7

背景：求每个classThe oldest student under the category

数据准备：

1 张三 15 1
2 李四 16 1
3 王五 16 2
4 赵六 15 2
5 钱七 14 2
6 孙八 16 2
7 李九 15 1
8 周十 16 1

在Mysql中,通过GroupBy分组后,will take the primary keyIDThe smallest piece of data is used as the grouped data.If there is demand,After sorting according to other fields, take the maximum or minimum value of the corresponding field,通过SQLStatements are achievable,The core is that it needs to be added after sortingLimit xx.如果不加Limit xx,Then the data for each group will be selected by defaultid最小的那条记录.

区别如下：

select * from 
(select * from student2 ORDER BY age desc limit 1000) record
GROUP BY record.class;

select * from 
(select * from student2 ORDER BY age desc) record
GROUP BY record.class;

思路：

1.先排序

2.Then group and take the first record of sorting

2.通过max(), min()It can realize the function of sorting first and then grouping（Applies to no duplicate records）

背景：求不同typeA record of the most recent time operation of type

数据准备：

id name type create_time
1 记录一 1 2022-07-31 14:17:42
2 记录二 2 2022-07-31 14:18:02
3 记录三 1 2022-07-31 14:18:16
4 记录四 2 2022-07-31 14:18:27
5 记录五 1 2022-07-31 14:18:48

select * from record a right join
(select type as b_type,max(create_time) as max_create_time from record GROUP BY type) b
on a.type = b.b_type and a.create_time = b.max_create_time

思路;

1.先用maxto find out the time max

2.然后用right join 关联主表, Find out the full amount of information