æ¥è¯¢å¯ç¨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;
ç»æï¼
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/0df3d7ca7bcb0a462855dd856d63f6246b60af13?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
3ãå é¤éå¤è®°å½ç¨è¯å¥ï¼
delete from test where rowid not in (select min(rowid) from test group by id,name);
commit;