ÎÄ/Ray
×öÏîÄ¿µÄʱºò£¬Ò»Î»Í¬Êµ¼Êý¾ÝµÄʱºò£¬²»Ð¡ÐÄ°ÑÒ»¸ö±íÖеÄÊý¾ÝÈ«¶¼¸ãÖØÁË£¬Ò²¾ÍÊÇ˵£¬Õâ¸ö±íÀïËùÓеļǼ¶¼ÓÐÒ»ÌõÖظ´µÄ¡£Õâ¸ö±íµÄÊý¾ÝÊÇǧÍò¼¶µÄ£¬¶øÇÒÊÇÉú²úϵͳ¡£Ò²¾ÍÊÇ˵£¬²»ÄÜ°ÑËùÓеļǼ¶¼É¾³ý£¬¶øÇÒ±ØÐë¿ìËٵİÑÖظ´¼Ç¼ɾµô¡£
¶Ô´Ë£¬×ܽáÁËÒ»ÏÂɾ³ýÖظ´¼Ç¼µÄ·½·¨£¬ÒÔ¼°Ã¿ÖÖ·½·¨µÄÓÅȱµã¡£
ΪÁ˳ÂËß·½±ã£¬¼ÙÉè±íÃûΪTbl£¬±íÖÐÓÐÈýÁÐcol1£¬col2£¬col3£¬ÆäÖÐcol1£¬col2ÊÇÖ÷¼ü£¬²¢ÇÒ£¬col1£¬col2ÉϼÓÁËË÷Òý¡£
1¡¢Í¨¹ý´´½¨ÁÙʱ±í
¿ÉÒÔ°ÑÊý¾ÝÏȵ¼Èëµ½Ò»¸öÁÙʱ±íÖУ¬È»ºóɾ³ýÔ±íµÄÊý¾Ý£¬ÔÙ°ÑÊý¾Ýµ¼»ØÔ±í£¬SQLÓï¾äÈçÏ£º
creat table tbl_tmp (select distinct* from tbl);
truncate table tbl;//Çå¿Õ±í¼Ç¼
insert into tbl select * from tbl_tmp;//½«ÁÙʱ±íÖеÄÊý¾Ý²å»ØÀ´¡£
ÕâÖÖ·½·¨¿ÉÒÔʵÏÖÐèÇ󣬵«ÊǺÜÃ÷ÏÔ£¬¶ÔÓÚÒ»¸öǧÍò¼¶¼Ç¼µÄ±í£¬ÕâÖÖ·½·¨ºÜÂý£¬ÔÚÉú²úϵͳÖУ¬Õâ»á¸øϵͳ´øÀ´ºÜ´óµÄ¿ªÏú£¬²»¿ÉÐС£
2¡¢ÀûÓÃrowid
ÔÚoracleÖУ¬Ã¿Ò»Ìõ¼Ç¼¶¼ÓÐÒ»¸örowid£¬rowidÔÚÕû¸öÊý¾Ý¿âÖÐÊÇΨһµÄ£¬rowidÈ·¶¨ÁËÿÌõ¼Ç¼ÊÇoracleÖеÄÄÄÒ»¸öÊý¾ÝÎļþ¡¢¿é¡¢ÐÐÉÏ¡£ÔÚÖظ´µÄ¼Ç¼ÖУ¬¿ÉÄÜËùÓÐÁеÄÄÚÈݶ¼Ïàͬ£¬µ«rowid²»»áÏàͬ¡£SQLÓï¾äÈçÏ£º
delete from tbl where rowid in (select a.rowid from tbl a, tbl b where a.rowid>b.rowid and a.col1=b.col1 and a.col2 = b.col2)
Èç¹ûÒѾ֪µÀÿÌõ¼Ç¼ֻÓÐÒ»ÌõÖظ´µÄ£¬Õâ¸ösqlÓï¾äÊÊÓᣵ«ÊÇÈç¹ûÿÌõ¼Ç¼µÄÖظ´¼Ç¼ÓÐNÌõ£¬Õâ¸öNÊÇδ֪µÄ£¬¾ÍÒª¿¼ÂÇÊÊÓÃÏÂÃæÕâÖÖ·½·¨ÁË¡£
3¡¢ÀûÓÃmax»òminº¯Êý
ÕâÀïҲҪʹÓÃrowid£¬ÓëÉÏÃ治ͬµÄÊǽáºÏmax»òminº¯ÊýÀ´ÊµÏÖ¡£SQLÓï¾äÈçÏÂ
delete from tbl a where rowid not in (select max(b.rowid) from tbl b where a.col1=b.col1 and a.col2 = b.col2);//ÕâÀïmaxʹÓÃminÒ²¿ÉÒÔ
»òÕßÓÃÏÂÃæµÄÓï¾ä
delete from tbl a where rowid < (select max(b.rowid) from tbl b where a.col1=b.col1 and a.col2 = b.col2);//ÕâÀïÈç¹û°Ñmax»»³ÉminµÄ»°£¬Ç°ÃæµÄwhere×Ó¾äÖÐÐèÒª°Ñ"<"¸ÄΪ">"
¸úÉÏÃæµÄ·½·¨Ë¼Â·»ù±¾ÊÇÒ»ÑùµÄ£¬²»¹ýʹÓÃÁËgroup by£¬¼õÉÙÁËÏÔÐԵıȽÏÌõ¼þ£¬Ìá¸ßЧÂÊ¡£SQLÓï¾äÈçÏ£º
delete from tbl where rowid not in (select max(rowid) from tbl t group by t.col1, t.col2 );
delete from tbl where (col1, col2) in (select col1,col2 from tbl group by col1,col2 having count(*) > 1) and rowid not in (select nin(rowid) from tbl group by col1,col2 having count(*) > 1)
»¹ÓÐÒ»ÖÖ·½·¨£¬¶ÔÓÚ±íÖÐÓÐÖظ´¼Ç¼µÄ¼Ç¼±È½ÏÉٵģ¬²¢ÇÒÓÐË÷ÒýµÄÇé¿ö£¬±È½ÏÊÊÓ᣼ٶ¨col1£¬col2ÉÏÓÐË÷Òý£¬²¢ÇÒtbl±íÖÐÓÐÖظ´¼Ç¼µÄ¼Ç¼±È½ÏÉÙ£¬SQLÓï¾äÈçÏÂ4¡¢ÀûÓÃgroup by£¬Ìá¸ßЧÂÊ