**create table emp1 as select * from emp;
insert into emp1 select * from emp1 where deptno=30;**

1、查詢 表中是否含有重複的資料
select count(*),empno from emp1 group by empno having count(*)>=2;
2、查詢表中重複資料

select * from emp1 where empno in (select empno from emp1 group by empno having count(empno) > 1) order by 1;

3、透過rowid查詢出需要去除的重複資料
select empno,min(rowid) from emp1 group by empno having count(empno)>1;

4、刪除重複資料庫(透過rowid或empno )
方法一：
delete from emp1 where
rowid in
(select min(rowid) from emp1 group by empno having count(empno)>1);
方法二：
delete from emp1 where
empno in
(select empno from emp1 group by empno having count(empno)>1)
and
rowid not in

(select min(rowid) from emp1 group by empno having count(empno)>1);

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！

Oracle 重複資料處理

Oracle 重複資料處理

create table emp1 as select * from emp; insert into emp1 select * from emp1 where deptno=30;

相關文章

**create table emp1 as select * from emp;
insert into emp1 select * from emp1 where deptno=30;**