-- 有! 如果表中没有主键的话,直接用rowid 去指定条件删除 -- (或借助中间表: -- Step 1: create table 中间表 as select distint * from 源表; -- Step 2: truncate table 源表; -- Step 3: insert into 源表 select * from 中间表 )
可以的 select distinct * from tb
create table t1 (id number, name varchar2(20)); insert into t1 values (1, 'hello'); insert into t1 values (1, 'hello'); insert into t1 values (1, 'hello'); insert into t1 values (2, 'world'); commit; select * from t1; ID NAME ---------- -------------------- 1 hello 1 hello 1 hello 2 worlddelete from t1 where rowid in ( select rid from (select rowid rid, id, name, row_number() over (partition by id, name order by id) rn from t1) where rn > 1); commit; select * from t1; ID NAME ---------- -------------------- 1 hello 2 world
-- (或借助中间表:
-- Step 1: create table 中间表 as select distint * from 源表;
-- Step 2: truncate table 源表;
-- Step 3: insert into 源表 select * from 中间表 )
select distinct * from tb
create table t1 (id number, name varchar2(20));
insert into t1 values (1, 'hello');
insert into t1 values (1, 'hello');
insert into t1 values (1, 'hello');
insert into t1 values (2, 'world');
commit;
select * from t1; ID NAME
---------- --------------------
1 hello
1 hello
1 hello
2 worlddelete from t1
where rowid in (
select rid from (select rowid rid, id, name, row_number() over (partition by id, name order by id) rn from t1)
where rn > 1);
commit;
select * from t1;
ID NAME
---------- --------------------
1 hello
2 world