Oracle删除重复记录的实现方法

数据库 Oracle
如果在Oracle数据库中出现了重复的记录,我们就需要用到删除操作,下面就教您几个查看重复记录并删除的方法,供您参考借鉴。

删除重复记录的SQL语句是我们最常用的语句之一,下面就为您将介绍Oracle删除重复记录的SQL语句写法,希望对您能有所帮助。

1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select * from people
where peopleId in (select   peopleId from   people group by   peopleId having count(peopleId) > 1)

2、Oracle删除重复记录,重复记录是根据单个字段(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)

注:rowid为oracle自带不用该.....

查找表中多余的重复记录(多个字段)
select * from vitae a
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)4、Oracle删除重复记录(多个字段),只留有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)

查找表中多余的重复记录(多个字段),不包含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)

 

 

 

【编辑推荐】

oracle查询当前时间的实现

带您了解Oracle层次查询

带您深入了解Oracle临时表

oracle查询用户所有表的语句

Oracle with语句的用法

责任编辑:段燃 来源: 互联网
相关推荐

2010-10-13 17:07:46

MySQL删除重复记录

2011-05-24 10:04:39

Oracle重复记录

2010-11-23 14:26:02

MySQL删除重复记录

2010-10-27 16:56:05

Oracle重复记录

2011-03-04 17:30:42

Oracle数据库

2010-09-03 09:49:39

SQL删除

2010-10-13 17:13:17

MySQL重复记录

2010-11-25 15:43:02

MYSQL查询重复记录

2010-09-25 16:17:25

SQL语句

2010-11-15 14:42:03

Oracle查询重复记

2010-04-13 10:23:40

Oracle数据库

2010-09-03 11:42:04

SQL删除

2011-05-17 11:23:02

Oracle数据库

2010-09-28 15:46:22

SQL删除重复记录

2010-09-28 15:40:51

SQL删除重复记录

2010-07-26 17:00:11

SQL Server查

2010-09-30 10:29:56

DB2删除重复记录

2010-07-02 13:50:11

SQL Server数

2010-11-19 13:14:21

Oracle删除归档日

2010-11-15 14:07:16

Oracle取固定记录
点赞
收藏

51CTO技术栈公众号