ID(number型) Name(varchar2型)
1 a
2 b
3 b
4 a
5 c
6 c
要求:执行一个删除语句，当Name列上有相同时,只保留ID这列上值小的
如何在提取出成员的同时，进行比较 求教
------解决方案--------------------
- SQL code
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