本文摘自PHP中文网,作者coldplay.xixi,侵删。
sql去重查询的方法:重复记录根据单个字段peopleId来判断,使用语句删除,代码为【where peopleId in (select peopleId from people group by peopleId 】。
sql去重查询的方法:
sql 单表/多表查询去除重复记录
单表distinct
多表group by
group by 必须放在 order by 和 limit之前,不然会报错
1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
1 2 | select * from people
where peopleId in (select peopleId from people group by peopleId having count (peopleId) > 1)
|
2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
1 2 3 | 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、查找表中多余的重复记录(多个字段)
1 2 | select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count (*) > 1)
|
4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
1 2 3 | 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最小的记录
1 2 3 | 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)
|
(二)
比方说
在A表中存在一个字段“name”,
而且不同记录之间的“name”值有可能会相同,
现在就是需要查询出在该表中的各记录之间,“name”值存在重复的项;
1 | Select Name, Count (*) From A Group By Name Having Count (*) > 1
|
如果还查性别也相同大则如下:
1 | Select Name,sex, Count (*) From A Group By Name,sex Having Count (*) > 1
|
(三)
阅读剩余部分
相关阅读 >>
sql server 2008 处理隐式数据类型转换在执行计划中的增强
sql server sql高级查询语句小结
sqlserver 批量数据替换助手v1.0版发布
sql是什么?
mysql数据库索引的最左匹配原则
spark sql数据加载和保存实例讲解
sql中的数据操作语句不包括哪个?
mysql中字符集详细介绍
springmvc4+mybatis+sql server2014实现数据库读写分离
mysql缓存的查询和清除命令使用详解
更多相关阅读请进入《sql》频道 >>
机械工业出版社
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。
转载请注明出处:木庄网络博客 » sql如何去重查询