delete from emp where emp_no in (select emp_no from emp group by emp_no having count(emp_no)>1);
delete from EMP where rowid > (select min(rowid) from EMP group by Emp_no)
delete from emp_no where rownum in( select min(rownum) from emp group by emp_no having count(emp_no)>1)
实测数据:CREATE TABLE emp ( EmpNo VARCHAR2(20), NAME VARCHAR2(20), TYPE VARCHAR2(20), Salary NUMBER(8, 2), Bonus NUMBER(8, 2) );INSERT INT emp VALUES('A001', '严忠斌', '正式', 2900.00, 2400.00); INSERT INT emp VALUES('A002', '蔺万国', '正式', 2900.00, 2100.00); INSERT INT emp VALUES('A001', '严忠斌', '正式', 2900.00, 2400.00); INSERT INT emp VALUES('A003', '蔡利平', '试用', 2600.00, 1900.00); INSERT INT emp VALUES('A002', '蔺万国', '正式', 2900.00, 2100.00); INSERT INT emp VALUES('B001', '张和平', '试用', 1700.00, 1600.00); INSERT INT emp VALUES('B002', '陈延展', '正式', 1400.00, 1450.00); INSERT INT emp VALUES('B003', '李红梅', '正式', 1250.00, 1050.00); INSERT INT emp VALUES('C001', '刘梅', '试用', 1300.00, 800.00); INSERT INT emp VALUES('C002', '李丽', '正式', 1400.00, 700.00); 实测结果:
以测试过删除了重复记录 delete from emp where empno in (select empno from emp group by empno having count(empno) > 1) and rowid not in (select min(rowid) from emp group by empno having count(empno) > 1)
delete from emp a where rowid<>(select min(rowid) from emp b where a.empno=b.empno)
SELECT EmpNo,max(Name) Name,max(Type) Type,max(Salary) Salary,max(Bonus) Bonus FROM EMP Group By EmpNo Having Count(*)>=1 Order By EmpNo Asc
cursor t_name is/*提取有重得的名字*/ select name from fuck group by name having count(name) >1;
begin v_sql:='delete from fuck where id in (select id from (select t.*, rownum r from fuck t where name = :1) where r > 1) ';
for t_names in t_name loop /*循环读取并删除*/ execute immediate v_sql using t_names.name; dbms_output.put_line('成功删除重复数据: '||t_names.name); end loop; end; /*********** 方式二 创建临时表 并查询出数据不重复的字段***************/ create global temporary table tempTable on commit preserve rows as select distinct id,name from fucks; /***********删除原有的表***************/ drop table fucks; /***********把监时表插入到 新建原有表***************/ create table fucks as select * from tempTable;drop table tempTable;--需要重新登陆才能删除临时表select * from fucks; select * from tempTable;--方式二 delete from fucks f1 where rownum not in (select min(rownum) from fucks f2 where f1.id = f2.id and f1.name = f2.name)
--方式二 delete from fucks f1 where rowid not in (select min(rowid) from fucks f2 where f1.id = f2.id and f1.name = f2.name)上在写错了,做测试时写的。请把表名修改下就可以了!我在测试时 做的是所有字段相同的数据,因为我面试过一家公司他们就是这么要求的!三种方式都测试通过了
where rowid > (select min(rowid) from EMP
group by Emp_no)
select min(rownum) from emp group by emp_no having count(emp_no)>1)
(
EmpNo VARCHAR2(20),
NAME VARCHAR2(20),
TYPE VARCHAR2(20),
Salary NUMBER(8, 2),
Bonus NUMBER(8, 2)
);INSERT INT emp VALUES('A001', '严忠斌', '正式', 2900.00, 2400.00);
INSERT INT emp VALUES('A002', '蔺万国', '正式', 2900.00, 2100.00);
INSERT INT emp VALUES('A001', '严忠斌', '正式', 2900.00, 2400.00);
INSERT INT emp VALUES('A003', '蔡利平', '试用', 2600.00, 1900.00);
INSERT INT emp VALUES('A002', '蔺万国', '正式', 2900.00, 2100.00);
INSERT INT emp VALUES('B001', '张和平', '试用', 1700.00, 1600.00);
INSERT INT emp VALUES('B002', '陈延展', '正式', 1400.00, 1450.00);
INSERT INT emp VALUES('B003', '李红梅', '正式', 1250.00, 1050.00);
INSERT INT emp VALUES('C001', '刘梅', '试用', 1300.00, 800.00);
INSERT INT emp VALUES('C002', '李丽', '正式', 1400.00, 700.00);
实测结果:
delete from emp
where empno in
(select empno from emp group by empno having count(empno) > 1)
and rowid not in
(select min(rowid) from emp group by empno having count(empno) > 1)
Group By EmpNo Having Count(*)>=1
Order By EmpNo Asc
declare
v_sql varchar2(500);
v_tempName varchar2(30);
cursor t_name is/*提取有重得的名字*/
select name from fuck group by name having count(name) >1;
begin
v_sql:='delete from fuck
where id in (select id
from (select t.*, rownum r from fuck t where name = :1)
where r > 1) ';
for t_names in t_name loop /*循环读取并删除*/
execute immediate v_sql using t_names.name;
dbms_output.put_line('成功删除重复数据: '||t_names.name);
end loop;
end;
/*********** 方式二 创建临时表 并查询出数据不重复的字段***************/
create global temporary table tempTable on commit preserve rows
as select distinct id,name from fucks;
/***********删除原有的表***************/
drop table fucks;
/***********把监时表插入到 新建原有表***************/
create table fucks as select * from tempTable;drop table tempTable;--需要重新登陆才能删除临时表select * from fucks;
select * from tempTable;--方式二
delete from fucks f1
where rownum not in (select min(rownum)
from fucks f2
where f1.id = f2.id
and f1.name = f2.name)
delete from fucks f1
where rowid not in (select min(rowid)
from fucks f2
where f1.id = f2.id
and f1.name = f2.name)上在写错了,做测试时写的。请把表名修改下就可以了!我在测试时 做的是所有字段相同的数据,因为我面试过一家公司他们就是这么要求的!三种方式都测试通过了