mysql查找删除重复数据并只保留一条实例详解
作者:lqh 时间:2024-06-05 09:52:53
有这样一张表,表数据及结果如下:
school_id | school_name | total_student | test_takers |
1239 | Abraham Lincoln High School | 55 | 50 |
1240 | Abraham Lincoln High School | 70 | 35 |
1241 | Acalanes High School | 120 | 89 |
1242 | Academy Of The Canyons | 30 | 30 |
1243 | Agoura High School | 89 | 40 |
1244 | Agoura High School | 100 | 50 |
我们可以看出,school_name的字段值有重复数据(Abraham Lincoln High School 和Agoura High School分别出现两次),那么如何删除这两条数据,从而只让这两个数值出现一次呢? 具体实现方法如下:
1、删除重复记录,保存Id最小的一条
delete FROM `test` WHERE `school_name` in (SELECT `school_name`
FROM `test`
GROUP BY `school_name`
HAVING COUNT( * ) >1) and school_id not in (select min(school_id) from test group by school_id having count(* )>1)
先使用GROUP BY having语法查询出重复的数据,然后删除重复数据并保留school_id最小的一条.
2、删除重复记录,保存Id最大的一条
delete FROM `test` WHERE `school_name` in (SELECT `school_name`
FROM `test`
GROUP BY `school_name`
HAVING COUNT( * ) >1) and school_id not in (select max(school_id) from test group by school_id having count(* )>1)
原理和上面一样。
标签:mysql,查找,删除,重复数据
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Windows7 64位安装最新版本MySQL服务器的图文教程
2024-01-28 18:39:24
![](https://img.aspxhome.com/file/2023/4/88484_0s.png)
opencv+python实现鼠标点击图像,输出该点的RGB和HSV值
2023-05-16 13:37:20
![](https://img.aspxhome.com/file/2023/1/69471_0s.jpg)
pytorch 带batch的tensor类型图像显示操作
2023-06-02 08:47:26
![](https://img.aspxhome.com/file/2023/2/120462_0s.jpg)
60个vue常用工具类
2024-06-07 16:03:50
vue parseHTML源码解析hars end comment钩子函数
2024-06-05 15:29:14
python的格式化输出(format,%)实例详解
2022-03-20 04:17:57
sql server 常用的几个数据类型
2024-01-14 16:39:34
python如何给内存和cpu使用量设置限制
2021-03-04 00:43:00
MySQL中锁的相关问题
2024-01-13 09:49:33
![](https://img.aspxhome.com/file/2023/3/71773_0s.png)
Python实现将照片变成卡通图片的方法【基于opencv】
2023-06-08 11:32:54
php 参数过滤、数据过滤详解
2023-11-17 17:36:56
利用Python实现眨眼计数器的示例代码
2021-04-16 10:39:53
![](https://img.aspxhome.com/file/2023/0/110010_0s.jpg)
完美解决SQL server2005中插入汉字变成问号的问题
2024-01-24 20:59:59
cnpm不是内部命令的解决方案:配置环境变量【推荐】
2023-03-11 13:27:47
![](https://img.aspxhome.com/file/2023/5/131925_0s.jpg)
Python中的tkinter库简单案例详解
2021-01-20 14:25:16
设计能力决定权力
2009-06-16 14:48:00
![](https://img.aspxhome.com/file/UploadPic/20096/16/d-68s.jpg)
Python 运行 shell 获取输出结果的实例
2023-08-02 16:51:18
Python实例解析图像形态学运算技术
2021-04-14 10:34:24
![](https://img.aspxhome.com/file/2023/3/135143_0s.png)
appium+python adb常用命令分享
2022-12-27 09:16:24
![](https://img.aspxhome.com/file/2023/7/80537_0s.jpg)
解决win7操作系统Python3.7.1安装后启动提示缺少.dll文件问题
2021-08-09 00:56:29
![](https://img.aspxhome.com/file/2023/3/112743_0s.png)