¿ìËÙɾ³ýoracleÖظ´¼Ç¼
×ܽáÁËÒ»ÏÂɾ³ýÖظ´¼Ç¼µÄ·½·¨£¬ÒÔ¼°Ã¿ÖÖ·½·¨µÄÓÅȱµã¡£
¼ÙÉè±íÃûΪ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
4¡¢ÀûÓÃgroup by£¬Ìá¸ßЧÂÊ
ƽʱ¹¤×÷ÖпÉÄÜ»áÓöµ½µ±ÊÔͼ¶Ô¿â±íÖеÄijһÁлò¼¸Áд´½¨Î¨Ò»Ë÷Òýʱ£¬ÏµÍ³Ìáʾ ORA-01452 £º²»ÄÜ´´½¨Î¨Ò»Ë÷Òý£¬·¢ÏÖÖظ´¼Ç¼¡£
ÏÂÃæ×ܽáһϼ¸ÖÖ²éÕÒºÍɾ³ýÖظ´¼Ç¼µÄ·½·¨£¨ÒÔ±íCZΪÀý£©£º
±íCZµÄ½á¹¹ÈçÏ£º
SQL> desc cz
Name Null? Type
----------------------------------------- -------- ------------------
C1 NUMB
Ïà¹ØÎĵµ£º
±¾ÎÄÖ÷Òª´Ó´óÐÍÊý¾Ý¿âORACLE»·¾³Ëĸö²»Í¬¼¶±ðµÄµ÷Õû·ÖÎöÈëÊÖ£¬·ÖÎöORACLEµÄϵͳ½á¹¹ºÍ¹¤×÷»úÀí£¬´Ó¾Å¸ö²»Í¬·½Ãæ½ÏÈ«ÃæµØ×ܽáÁËORACLEÊý¾Ý¿âµÄÓÅ»¯µ÷Õû·½°¸¡£
¹Ø¼ü´Ê ORACLEÊý¾Ý¿â»·¾³µ÷ÕûÓÅ»¯Éè¼Æ·½°¸
¶ÔÓÚORACLEÊý¾Ý¿âµÄÊý¾Ý´æÈ¡£¬Ö÷ÒªÓÐËĸö²»Í¬µÄµ÷Õû¼¶±ð£¬µÚÒ»¼¶µ÷ÕûÊDzÙ×÷ÏµÍ ......
MySQLµÄID·Ç³£·½±ã¶¨Ò壬ֻҪָ¶¨Æä×ֶεÄ×Ô¶¯ÔöÁ¿ÊôÐÔ¼´¿É¡£
µ«ÊÇOracle²»ÐУ¬ÐèÒª¶¨ÒåsequenceºÍtriggedr£¬µ±È»trigger¿ÉÒÔ²»¶¨Ò壬µ«ÊDz»·½±ã¡£
±í¶¨ÒåÈçÏ£º
CREATE TABLE GAME
(
ID
INTEGER ......
Lately,
Jordan bank upgraded their ICBS banking software to Oracle 9i and
IDS9i. The Bank also moved from a decentralized to a centralized
system.
We were contracted to monitor and diagnose performance issues during the launching phase of the new system.
The IBM server was equipped with 32 GB o ......
ÔÚOracleÖÐʹÓÃGuid
ÔÚOracleÖпÉÒÔÓÃSYS_GUID()À´Éú³ÉÒ»¸öguid,Ï൱ÓÚmsSqlÖеÄnewid()¡£
ÔÚOracle9iºÍOracle 10g ÀïSYS_GUID²úÉúµÃµ½µÄÊý¾ÝÊÇ32 λµÄ£¬È磺234E45F0077881AAE0430AA3034681AA
µ«OracleÖеÄguidÓëmsSqlÖеÄguid¸ñʽ²»ÍêÈ«Ïàͬ¡£
½«OracleÖеÄguid·Ö¸î³ÉΪ Windowsϵͳ ObjectId µÄ¸ñʽ£º{8-4-4-4-12} £ ......