ºìÁªLinuxÃÅ»§
Linux°ïÖú

Oracle¿ìËÙɾ³ýÖظ´µÄ¼Ç¼

·¢²¼Ê±¼ä:2006-06-27 11:37:47À´Ô´:ºìÁª×÷Õß:ckaces
ÎÄ/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£¬Ìá¸ßЧÂÊ
ÎÄÕÂÆÀÂÛ

¹²ÓÐ 0 ÌõÆÀÂÛ