æ¥è¯¢å¯ç¨group byè¯å¥ï¼å é¤åç¨deleteè¯å¥ã
1ãå建æµè¯è¡¨ï¼æå
¥æµè¯æ°æ®ï¼
create table test
(id int,
name varchar2(20));
insert into test values (1,'å¼ ä¸');
insert into test values (1,'å¼ ä¸');
insert into test values (2,'æå');
insert into test values (2,'æå');
insert into test values (3,'çäº');
insert into test values (3,'çäº');
insert into test values (3,'çäº');
insert into test values (4,'èµµå
');
commit;
2ãæ¥è¯¢éå¤æ°æ®ï¼ç¨è¯å¥ï¼
select id,name from test group by id,name having count(*)>1;
ç»æï¼

3ãå é¤éå¤è®°å½ç¨è¯å¥ï¼
delete from test where rowid not in (select min(rowid) from test group by id,name);
commit;