oracle删除重复记录的方法(oracle删除重复数据)

比如我们有以下表

create table emp as select * from employees;

看看记录数:

select count(*) from emp;

--

107

然后,重复插入几次:

insert into emp select * from emp;

insert into emp select * from emp;

insert into emp select * from emp;

insert into emp select * from emp;

然后看这个emp表中有很多重复记录,

那么我们如何快速删除呢,

我们可以使用oracle的分析函数over partition来处理:

DELETE FROM emp

WHERE ROWID IN

(SELECT ROWID

FROM (SELECT ROWID,

ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY employee_id) rn

FROM emp)

WHERE rn > 1);

之后,我们再去看,就只有107行记录了.


oracle如何删除重复数据只保留一条

原文链接 http://t.zoukankan.com/c-Ajing-p-13448349.html

在实际开发中,可能会遇到数据库多条数据重复了,此时我们需要删除重复数据,只保留一条有效数据,用SQL语句怎么实现呢,下面我们模拟一下:

1.准备重复数据:



2.过滤出存在重复数据的信息:

SQL语句:

SELECTdname FROMdept GROUP BYdname HAVINGcount( dname ) >1

数据库筛选结果:



3.从重复数据中筛选出一条需要保存的数据:

SQL语句:

SELECTmin( deptno ) -- max( deptno )FROMdept GROUP BYdname HAVINGcount( dname ) >1

这里使用数据库的MIN或者MAX函数筛选出一条需要保存数据即可。

数据库筛选结果:



4.删除重复数据,即从重复的数据中,删除掉需要保留数据之外的所有信息:

SQL:

DELETE FROMdept WHEREdname IN ( SELECT dname FROM dept GROUP BY dname HAVING count( dname ) > 1 ) -- 过滤出重复的dnameAND deptno NOT IN ( SELECT min( deptno ) AS deptno FROM dept GROUP BY dname HAVING count( dname ) > 1 ) -- 过滤出不在需要保留的id之外的所有id

如果是oracle数据库,上面的SQL语句可以正常执行,可是换成mysql数据库,SQL语句执行会报错:

1093 - You can't specify target table 'dept' for update in FROM clause

含义:不能在同一表中查询的数据作为同一表的更新数据。

适用于mysql数据库的SQL:

DELETE FROMdept WHEREdname IN ( SELECT * FROM ( SELECT dname FROM dept GROUP BY dname HAVING count( dname ) > 1 ) a ) AND deptno NOT IN ( SELECT * FROM ( SELECT min( deptno ) AS deptno FROM dept GROUP BY dname HAVING count( dname ) > 1 ) b )

数据库最后数据:



到这里,删除重复数据成功!

一颗安安静静的小韭菜。文中如果有什么错误,欢迎指出。

oracle删除重复记录保留一条

我们在使用数据库的时候,有时数据会有所重复,当我们只需要一项数据时,不需要显示重复的记录时,就可以用以下SQL代码:

--查找表中多余的重复记录,重复记录是根据单个字段来判断select * from people where peopleId in(select peopleId from people group by peopleIdhaving count(peopleId)>1);--删除表中多余的重复记录,重复记录是根据单个字段来判断,只保留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);--查找表中多余的重复记录(多个字段)select * from vitae awhere (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*)>1);--删除表中多余的重复记录(多个字段),只保留rowid最小的记录delete from vitae awhere (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);