mysql过滤表中重复数据_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

+----+----------+-------------------+

| id | area | best_history_data |

+----+----------+-------------------+

|1 | beijing| 33|

|4 | shanghai | 2 |

|6 | chengdu| 2 |

|7 | henan| 13|

+----+----------+-------------------+

原文：https://www.cnblogs.com/mianbaoshu/p/12058588.html