SQL> create table t( 2 id number, 3 name varchar2(20));表已创建。SQL> insert into t values(1,'a');已创建 1 行。SQL> insert into t values(2,'b');已创建 1 行。SQL> insert into t values(3,'b');已创建 1 行。SQL> insert into t values(4,'a');已创建 1 行。SQL> insert into t values(5,'c');已创建 1 行。SQL> insert into t values(6,'c');已创建 1 行。SQL> SQL> delete t 2 where exists(select 1 from (select row_number() over (partition by name order by id) rn,id from t) t1 3 where t.id=t1.id and t1.rn>1);已删除3行。SQL> SQL> select * from t; ID NAME ---------- ---------------------------------------- 1 a 2 b 5 c
SQL> create table t(
2 id number,
3 name varchar2(20));表已创建。SQL> insert into t values(1,'a');已创建 1 行。SQL> insert into t values(2,'b');已创建 1 行。SQL> insert into t values(3,'b');已创建 1 行。SQL> insert into t values(4,'a');已创建 1 行。SQL> insert into t values(5,'c');已创建 1 行。SQL> insert into t values(6,'c');已创建 1 行。SQL>
SQL> delete t
2 where exists(select 1 from (select row_number() over (partition by name order by id) rn,id from t) t1
3 where t.id=t1.id and t1.rn>1);已删除3行。SQL>
SQL> select * from t; ID NAME
---------- ----------------------------------------
1 a
2 b
5 c