sql怎么查询重复记录、删除重复记录？Jimcloud服务器出租 (产品供销 - 数码产品)
广告号码: 1620989 类别: 产品供销 - 数码产品
sql怎么查询重复记录、删除重复记录？Jimcloud服务器出租 | |
sql怎么查询重复记录、删除重复记录？ Jimcloud 香港 美国 新加坡 菲律宾 韩国 日本多IP服务器租用 E3/E5/双E5 8G / 16G / 32G / 64G Jimcloud海外服务器租用找Coke Telegram：@idcDJ Skype：live:.cid.8a666f2113038d7d 本篇文章重点为大家讲解一下sql查询重复记录、删除重复记录具体方法，有需要的小伙伴可以参考一下。 查找所有重复标题的记录： SELECT * FROM t_info a WHERE ((SELECT COUNT(*) FROM t_info WHERE Title = a.Title) > 1) ORDER BY Title DESC 一、查找重复记录 1.查找全部重复记录 Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1) 2.过滤重复记录(只显示一条) Select * From HZT Where ID In (Select Max(ID) From HZT Group By Title) 注：此处显示ID最大一条记录 二、删除重复记录 1.删除全部重复记录（慎用） Delete 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1) 2.保留一条记录 Delete HZT Where ID Not In (Select Max(ID) From HZT Group By Title) 注：此处保留ID最大一条记录 删除多余的重复记录 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 peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>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) | |
| 相关链接: (无) 面向省市区: 全国 面向市区县: 全部 最后更新: 2023-04-03 15:38:53 | 发 布 者: Coke 联系电话: (无) 电子邮箱: (无) 浏览次数: 73 |
© 2024 中国免费广告 CNFreeAds.com