删除SQL数据库表中的重复记录
在n条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复并保留一条呢?方法如下:
1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
[sql] view plain copy select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录 [sql] view plain copy 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、查找表中多余的重复记录(多个字段) [sql] view plain copy select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录 [sql] view plain copy 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最小的记录 [sql] view plain copy 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)
相关文章
- sql语句查询执行顺序详解数据库
- Oracle中使用SQL删除字段(oracle删除字段sql)
- MySQL动态SQL语句:构建数据库的基石(mysql动态sql语句)
- MySQL中SQL语句的使用方法(mysql中sql语句)
- SQL如何转换成MySQL,易学易用(sql转换成mysql)
- SQL与Oracle在数据库中的区别(sql和oracle区别)
- 优化Oracle数据库SQL优化实践指南(oracle执行sql)
- Exploring the Power of MySQL Stored Procedures with SQL(mysql存储过程sql)
- 使用 MySQL 外键优化 SQL 数据库设计(mysql外键sql)
- MySQL数据库的SQL语句执行过程(mysql执行sql)
- 数据库创建SQL Server数据库:一步一步指南(创建sqlserver)
- 解决方案:从SQL转移到MSSQL的步骤(sql转mssql)
- SQL Server单机版:全面高效的个人数据库管理(sqlserver单机版)
- PL/SQL连接MS SQL服务器:一种实现多数据库访问的桥梁(plsql链接mssql)
- Oracle终结SQL:解救数据库之路(oracle终止sql)
- 深入浅出:精通Oracle数据库SQL语句(oracle数据库sql语句)
- 深入比较Oracle与SQL的配置技巧(oracle与Sql配置)
- SQL建立数据库及删除数据库命令
- MySQL数据库设置远程访问权限方法小结