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

ÈçºÎʹÓÃOracleÖÐµÄ COALESCE º¯Êý

·¢²¼Ê±¼ä:2006-10-16 00:05:30À´Ô´:ºìÁª×÷Õß:smallwl
¡¡¡¡Êý¾Ý¿âÓ¦ÓÃÈí¼þºÜ¶àʱºò½«¶àÖصġ¢Ïà¹ØµÄʵÌåÐÅÏ¢±£´æÔÚͬһ¸ö±í¸ñÖС£ÀýÈ磬¹ºÂòµÄÁã¼þºÍ±¾µØÉú²úµÄÁã¼þ¶¼ÊÇÁã¼þ£¬¾­ÀíºÍ¹¤È˶¼ÊÇÔ±¹¤£¬¾¡¹Ü¶àÖصÄʵÌåÀàÐÍÔÚÊý¾Ý´æ´¢ÉÏÂÔÓв»Í¬£¬µ«ÊÇËüÃÇÓÐÌ«¶àµÄÏà֮ͬ´¦£¬Òò´Ëͨ³£Ê¹ÓÃÒ»¸ö±í¸ñ¶ø²»ÊÇÁ½¸ö¡£

¡¡¡¡´¦ÀíÕâÑùµÄ±í¸ñͨ³£Òâζ×ŶÔÿһÐнøÐÐÌõ¼þ²âÊÔÀ´¼ì²éÄĸöʵÌåÀàÐÍÕýÔÚ±»´¦Àí£¬È»ºó·µ»ØÿÖÖÀàÐ͵IJ»Í¬½á¹û¼¯¡£CASEÓï¾ä¿ÉÒÔÓÃÀ´Íê³ÉÕâÒ»¹¤×÷¡£

¡¡¡¡´ÓOracle 9i°æ¿ªÊ¼£¬COALESCEº¯ÊýÔںܶàÇé¿öϾͳÉΪÌæ´úCASEÓï¾äµÄÒ»Ìõ½Ý¾¶£¬COALESCEµÄ¸ñʽÈçÏÂ:

[code]COALESCE (expression_1, expression_2, ...,expression_n)[/code]

¡¡¡¡ÁбíÖеÚÒ»¸ö·Ç¿ÕµÄ±í´ïʽÊǺ¯ÊýµÄ·µ»ØÖµ£¬Èç¹ûËùÓеıí´ïʽ¶¼ÊÇ¿ÕÖµ£¬×îÖÕ½«·µ»ØÒ»¸ö¿ÕÖµ¡£

¡¡¡¡Ê¹ÓÃCOALESCEµÄÃØÃÜÔÚÓڴ󲿷ְüº¬¿ÕÖµµÄ±í´ïʽ×îÖÕ½«·µ»Ø¿ÕÖµ(Á¬½Ó²Ù×÷·û¡°||¡±ÊÇÒ»¸öÖµµÃ×¢ÒâµÄÀýÍâ)¡£ÀýÈ磬¿ÕÖµ¼ÓÈκÎÖµ¶¼ÊÇ¿ÕÖµ£¬¿ÕÖµ³ËÈκÎÖµÒ²¶¼ÊÇ¿ÕÖµ£¬ÒÀ´ËÀàÍÆ¡£

¡¡¡¡ÕâÑùÄú¾Í¿ÉÒÔ¹¹½¨Ò»ÏµÁбí´ïʽ£¬Ã¿¸ö±í´ïʽµÄ½á¹ûÊÇ¿ÕÖµ»ò·Ç¿Õ£¬Õâ¾ÍÏñÒ»¸öCASEÓï¾ä£¬±í´ïʽ°´ÕÕ˳Ðò½øÐвâÊÔ£¬µÚÒ»¸öÂú×ãÌõ¼þµÄÖµ¾ÍÈ·¶¨Á˽á¹û¡£

¡¡¡¡ÁбíAչʾÁËÃûΪPARTSµÄ±í¸ñµÄÒ»²¿·Ö£¬¸Ã±í¸ñ´æ´¢Á˹ºÂòµÄÁã¼þÊý¾ÝºÍÉú²úµÄÁã¼þÊý¾Ý£¬Èç¹ûÊǹºÂòµÄÁã¼þ£¬ÄÇôpart_typeÁеÄֵΪ¡®P¡¯£¬Èç¹ûÊDZ¾µØÉú²ú»ò×é×°µÄÔòÊÇ¡®B¡¯;´ËÍ⣬¶ÔÓÚ¹ºÂòµÄÁã¼þ£¬purchase_cost ÁлáÏÔʾ¹ºÂò³É±¾£¬¶ø±¾µØÉú²úµÄÁã¼þÔòÊÇ¿ÕÖµ;¶øÇÒ£¬±¾µØÉú²úµÄÁã¼þ»¹ÓÐmaterial_qtyºÍmaterial_costÁ½ÁеÄÐÅÏ¢£¬¶ÔÓÚ¹ºÂòµÄÁã¼þÔòÊÇ¿ÕÖµ¡£

¡¡¡¡ÁбíA£º

[code]SQL> desc parts
Name Null? Type
PART_ID NOT NULL NUMBER(6)
PART_TYPE NOT NULL CHAR(1)

(other columns)

PURCHASE_COST NUMBER(8,2)
MATERIAL_COST NUMBER(8,2)
MATERIAL_QTY NUMBER(6)
LAST_UPDATED TIMESTAMP(6)
UPDATED_BY VARCHAR2(30)[/code]

¡¡¡¡Äú¿ÉÒÔʹÓÃÒ»¸öCASEÓï¾äÀ´²âÊÔpart_typeÁеÄÖµ²¢·µ»Øeither purchase_costºÍmaterial_qtyÁÐÓëmaterial_costÁеij˻ý;²»¹ýCOALESCE¿ÉÒÔÓÃÒ»ÐÐÓï¾ä½â¾öÕâ¸öÎÊÌâ:

[code]COALESCE(purchase_cost, material_qty * material_cost)[/code]

¡¡¡¡Èç¹ûÊý¾ÝÐÐÖд洢µÄÊÇÒ»¸ö¹ºÂòµÄÁã¼þ£¬ÄÇôpurchase_cost¾Í²»ÊÇ¿ÕÖµ£¬½«·µ»Øpurchase_costµÄÖµ;È»¶ø£¬¶ÔÓÚ±¾µØÉú²úµÄÁã¼þ£¬purchase_costÊÇ¿ÕÖµ£¬COALESCE»áºöÂÔËü£¬È»ºó½«material_qtyºÍmaterial_costÏà³Ë£¬²¢½«³Ë»ý×÷Ϊ½á¹û·µ»Ø¡£

[code]SELECT part_id "Part", part_type "Type",
COALESCE(purchase_cost, material_qty * material_cost) "Cost"
FROM parts;[/code]

¡¡¡¡Äú¿ÉÒÔ¶ÔÈκÎÊýÁ¿µÄ±í´ïʽÖظ´Ê¹ÓÃÕâ¸öģʽ£¬COALESCEÊÇÒ»¸ö·Ç³£±ã½ÝµÄ·½·¨¶Ôͳһ±í¸ñÖеĶàÖØʵÌåÇóÖµ¡£

¡¡¡¡×îºó£¬»¹ÒªËµÒ»µãCASEÓï¾äµÄÓŵ㣬¾ÍÊÇCASEÊÇ×Ô¶¯½øÐÐÎĵµ¼Ç¼µÄ£¬Õâ±ãÓÚÀí½âºÍ½â¶ÁÕýÔÚ·¢ÉúµÄÊÂÇé¡£
ÎÄÕÂÆÀÂÛ

¹²ÓÐ 0 ÌõÆÀÂÛ