SQL语句实现删除重复记录并只保留一条
作者:junjie 时间:2024-01-13 21:19:23
delete WeiBoTopics where Id in(select max(Id) from WeiBoTopics group by WeiBoId,Title having COUNT(*) > 1);
SQL:删除重复数据,只保留一条用SQL语句,删除掉重复项只保留一条在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢
1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
delete from people where peopleName in (select peopleName from people group by peopleName having count(peopleName) > 1) and peopleId not in (select min(peopleId) from people group by peopleName having count(peopleName)>1)
3、查找表中多余的重复记录(多个字段)
select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
6.消除一个字段的左边的第一位:
update tableName set [Title]=Right([Title],(len([Title])-1)) where Title like '村%'
7.消除一个字段的右边的第一位:
update tableName set [Title]=left([Title],(len([Title])-1)) where Title like '%村'
8.假删除表中多余的重复记录(多个字段),不包含rowid最小的记录
update vitae set ispass=-1 where peopleId in (select peopleId from vitae group by peopleId,seq having count(*) > 1) and seq in (select seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
标签:SQL,删除,重复记录,保留一条
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
python中numpy的矩阵、多维数组的用法
2021-01-26 04:25:43
Python&Matlab实现樱花的绘制
2023-11-04 05:07:16
![](https://img.aspxhome.com/file/2023/2/63902_0s.gif)
python numpy库之如何使用matpotlib库绘图
2023-02-07 22:22:24
![](https://img.aspxhome.com/file/2023/2/104392_0s.png)
SQL Transcation的一些总结分享
2012-08-21 10:21:28
利用Pycharm将python文件打包为exe文件的超详细教程(附带设置文件图标)
2021-12-06 18:06:24
![](https://img.aspxhome.com/file/2023/8/93888_0s.png)
Golang开发gRPC服务入门介绍
2024-02-14 09:56:20
![](https://img.aspxhome.com/file/2023/7/105127_0s.jpg)
Python shelve模块实现解析
2023-06-23 16:58:01
![](https://img.aspxhome.com/file/2023/6/92166_0s.png)
如何查看连接MYSQL数据库的IP信息
2024-01-16 17:26:51
查看Django和flask版本的方法
2021-01-29 02:50:11
Python里字典的基本用法(包括嵌套字典)
2023-04-26 14:51:00
![](https://img.aspxhome.com/file/2023/7/95377_0s.png)
hadoop迁移数据应用实例详解
2022-12-28 01:29:45
![](https://img.aspxhome.com/file/2023/3/112083_0s.jpg)
从列表或字典创建Pandas的DataFrame对象的方法
2022-06-12 13:03:45
![](https://img.aspxhome.com/file/2023/2/130642_0s.png)
PHP 面向对象程序设计(oop)学习笔记 (二) - 静态变量的属性和方法及延迟绑定
2024-06-05 15:42:03
如何让框架的网页背景透明
2008-04-08 14:24:00
Python中functools模块函数解析
2021-03-11 10:15:40
在线Ajax载入动画生成工具 - Loadinfo
2008-02-18 13:34:00
![](https://img.aspxhome.com/file/UploadPic/20082/18/2008218134327641s.jpg)
Python批量修改图片分辨率的实例代码
2023-01-30 01:59:10
仿天涯底部固定漂浮导航,无JS纯CSS定义
2009-07-06 12:44:00
Python中使用wxPython开发的一个简易笔记本程序实例
2021-09-07 22:06:22
![](https://img.aspxhome.com/file/2023/2/97302_0s.png)
python爬虫 urllib模块url编码处理详解
2021-09-13 02:24:37