mysql过滤表中重复数据，查询相同数据的特定一条
待操作的表如下：
p.p1 { margin: 0; font: 16px Menlo; color: rgba(0, 0, 0, 1) }
span.s1 { font-variant-ligatures: no-common-ligatures }
select * from test_max_data;
p.p1 { margin: 0; font: 16px Menlo; color: rgba(0, 0, 0, 1) }
span.s1 { font-variant-ligatures: no-common-ligatures }
+----+----------+-------------------+
| 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，可用以下方式：
p.p1 { margin: 0; font: 16px Menlo; color: rgba(0, 0, 0, 1) }
span.s1 { font-variant-ligatures: no-common-ligatures }
select area,max(best_history_data) best_history_data from test_max_data group by area;
p.p1 { margin: 0; font: 16px Menlo; color: rgba(0, 0, 0, 1) }
span.s1 { font-variant-ligatures: no-common-ligatures }
+----------+-------------------+
| area | best_history_data |
+----------+-------------------+
| beijing | 33 |
| chengdu | 2 |
| henan | 13 |
| shanghai | 2 |
+----------+-------------------+
该方式只能获取到area和对应的最大best_history_data字段值。
2.使用联表查询的方式
p.p1 { margin: 0; font: 16px Menlo; color: rgba(0, 0, 0, 1) }
span.s1 { font-variant-ligatures: no-common-ligatures }
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;
p.p1 { margin: 0; font: 16px Menlo; color: rgba(0, 0, 0, 1) }
span.s1 { font-variant-ligatures: no-common-ligatures }
+----+----------+-------------------+
| id | area | best_history_data |
+----+----------+-------------------+
| 1 | beijing | 33 |
| 4 | shanghai | 2 |
| 6 | chengdu | 2 |
| 7 | henan | 13 |
+----+----------+-------------------+
3.使用相关子查询的方式
p.p1 { margin: 0; font: 16px Menlo; color: rgba(0, 0, 0, 1) }
span.s1 { font-variant-ligatures: no-common-ligatures }
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的方式
p.p1 { margin: 0; font: 16px Menlo; color: rgba(0, 0, 0, 1) }
span.s1 { font-variant-ligatures: no-common-ligatures }
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 |
+----+----------+-------------------+
参考：https://www.cnblogs.com/mianbaoshu/p/11821255.html
https://blog.csdn.net/Tenlay_Li/article/details/82704325
最新文章
- Android DEX 基础
- PHP curl报错“Problem (2) in the Chunked-Encoded data”解决方案
- 怎么改变 placeholder字体颜色
- mysql -workbench : Error cause by ' sql-mode = only-full-group-by'
- 循序渐进Python3（七） --1-- 面向对象
- jquery时间倒计时
- Delphi自定义窗口过程WinProc
- iOS执行时工具-cycript
- （转）关于CoreData的一个工具Mogenerator的使用
- Redis单机版以及集群版的安装搭建以及使用
- js apply
- CentOS7 安装zookeeper
- Struts2更改配置文件struts.xml默认路径
- php的文件引用
- mysql的复习
- C# 只开启一个程序，如果第二次打开则自动将第一个程序显示到桌面
- (stripTrailingZeros)A == B hdu2054
- Dll注入经典方法完整版
- 21OGNL与ValueStack(VS)-静态方法访问
- JVM概念总结：数据类型、堆与栈