mysql过滤表中重复数据，查询相同数据的特定一条
待操作的表如下：
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<b.best_history_data);
+----+----------+-------------------+
| id | area | best_history_data |
+----+----------+-------------------+
| 1 | beijing | 33 |
| 4 | shanghai | 2 |
| 6 | chengdu | 2 |
| 7 | henan | 13 |
+----+----------+-------------------+