待操作的表如下:
select * from test_max_data;
+----+----------+-------------------+
| id | area | best_history_data |
+----+----------+-------------------+
|1 | beijing| 33|
|2 | beijing| 22|
|3 | shanghai | 1 |
|4 | shanghai | 2 |
|5 | chengdu| 1 |
|6 | chengdu| 2 |
|7 | henan| 13|
|8 | henan| 12|
+----+----------+-------------------+
期待:获取各个area中best_history_data最大的一条
1.最简单的一种方式,如果只需要从表中获取area和对应的最大best_history_data,可用以下方式:
select area,max(best_history_data) best_history_data from test_max_data group by area;
+----------+-------------------+
| area | best_history_data |
+----------+-------------------+
| beijing| 33|
| chengdu| 2 |
| henan| 13|
| shanghai | 2 |
+----------+-------------------+
该方式只能获取到area和对应的最大best_history_data字段值。
2.使用联表查询的方式
select a.* from test_max_data a join (select area,max(best_history_data) best_history_data from test_max_data group by area) b where a.area=b.area and a.best_history_data=b.best_history_data;
+----+----------+-------------------+
| id | area | best_history_data |
+----+----------+-------------------+
|1 | beijing| 33|
|4 | shanghai | 2 |
|6 | chengdu| 2 |
|7 | henan| 13|
+----+----------+-------------------+
3.使用相关子查询的方式
select * from test_max_data a where a.best_history_data=(select max(best_history_data) as best_history_data from test_max_data where area=a.area);
+----+----------+-------------------+
| id | area | best_history_data |
+----+----------+-------------------+
|1 | beijing| 33|
|4 | shanghai | 2 |
|6 | chengdu| 2 |
|7 | henan| 13|
+----+----------+-------------------+
4.使用not exists的方式
select * from test_max_data a where not exists (select * from test_max_data b where a.area=b.area and a.best_history_data
+----+----------+-------------------+
| id | area | best_history_data |
+----+----------+-------------------+
|1 | beijing| 33|
|4 | shanghai | 2 |
|6 | chengdu| 2 |
|7 | henan| 13|
+----+----------+-------------------+