没啥好说的--以下是删除重复数据的3种方案 --3种方案都是最优化的 --object_name, object_id. 这2个字段用来判断重复--1. delete from x where rowid in ( select rd from ( select rowid rd ,row_number() over(partition by object_name, object_id order by rowid) rn from x ) x where rn > 1 )--2. delete from x where rowid not in ( select max(rowid) from x group by object_name, object_id )
--3. create table tmp_x AS select x1.(字段列表..略) from ( select x.*, row_number() over(partition by object_name, object_id order by rowid) rn from x ) x1 where rn = 1;
truncate table x;insert into x select * from tmp_x; drop table tmp_x;
-- 参考下 delete from employees where rowid in (select rid from (select rowid rid, row_number() over(partition by employee_id , last_name,department_id order by rowid) rn from employees) where rn <> 1);
假设表名为tab ,表的关键字为A delete from tab where a='您要删除的值' and rownum<(select count(*) from tab where a='您要删除的值')
--3种方案都是最优化的
--object_name, object_id. 这2个字段用来判断重复--1.
delete
from x
where rowid in
(
select rd
from (
select rowid rd
,row_number() over(partition by object_name, object_id order by rowid) rn
from x
) x
where rn > 1
)--2.
delete
from x
where rowid not in (
select max(rowid)
from x
group by object_name, object_id
)
--3.
create table tmp_x
AS
select x1.(字段列表..略)
from
(
select x.*, row_number() over(partition by object_name, object_id order by rowid) rn
from x
) x1
where rn = 1;
truncate table x;insert into x
select *
from tmp_x; drop table tmp_x;
delete from employees
where rowid in (select rid
from (select rowid rid,
row_number() over(partition by employee_id , last_name,department_id order by rowid) rn
from employees)
where rn <> 1);
delete from tab where a='您要删除的值' and rownum<(select count(*) from tab where a='您要删除的值')