将表中的记录id相同的删去重复的记录,只保存一条
附sql脚本:
prompt PL/SQL Developer import file
prompt Created on 2009年7月11日 星期六 by Administrator
set feedback off
set define off
prompt Disabling triggers for TEST...
alter table TEST disable all triggers;
prompt Loading TEST...
insert into TEST (ID, NAME)
values (100, 'aaa');
insert into TEST (ID, NAME)
values (101, 'bbb');
insert into TEST (ID, NAME)
values (103, 'ccc');
insert into TEST (ID, NAME)
values (105, 'ddd');
insert into TEST (ID, NAME)
values (104, 'eee');
insert into TEST (ID, NAME)
values (102, 'fff');
insert into TEST (ID, NAME)
values (102, 'ggg');
insert into TEST (ID, NAME)
values (101, 'hhh');
insert into TEST (ID, NAME)
values (105, 'iii');
commit;
prompt 9 records loaded
prompt Enabling triggers for TEST...
alter table TEST enable all triggers;
set feedback on
set define on
prompt Done.
附sql脚本:
prompt PL/SQL Developer import file
prompt Created on 2009年7月11日 星期六 by Administrator
set feedback off
set define off
prompt Disabling triggers for TEST...
alter table TEST disable all triggers;
prompt Loading TEST...
insert into TEST (ID, NAME)
values (100, 'aaa');
insert into TEST (ID, NAME)
values (101, 'bbb');
insert into TEST (ID, NAME)
values (103, 'ccc');
insert into TEST (ID, NAME)
values (105, 'ddd');
insert into TEST (ID, NAME)
values (104, 'eee');
insert into TEST (ID, NAME)
values (102, 'fff');
insert into TEST (ID, NAME)
values (102, 'ggg');
insert into TEST (ID, NAME)
values (101, 'hhh');
insert into TEST (ID, NAME)
values (105, 'iii');
commit;
prompt 9 records loaded
prompt Enabling triggers for TEST...
alter table TEST enable all triggers;
set feedback on
set define on
prompt Done.
from
(select id,name,row_number() over(partition by id order by name) rn from test) t where rn=1
delete from test where rowid not in (select min(rowid) from test group by id);
where
id in (
select max(id)
from test
group by id
)
select max(b.rowid)
from test b where a.id=b.id)
select max(b.rowid) from test group by id having count(*)>=2);多次执行;
delete from test where rowid in (
select max(rowid) from test group by id having count(*)>=2); 多次执行!
delete from test where rowid in (
select max(rowid) from test group by id having count(*)>=2); 查找
select id,name
from
(select aa,bb,row_number() over(partition by id order by name) rn from test) t where rn=1
delete test t where id not in(select max(rowid) from test where id=t.id);
delete test t where rowid not in(select max(rowid) from test where id=t.id);