sqlServer,oracleÖÐcase¹Ø¼ü×ÖµÄÓ÷¨
ÔÚдSQLʱ£¬ÓÐʱҪ¸ù¾ÝÒÑÓеÄÁÐÖµ¹¹ÔìеÄÁÐÖµ£¬Õâʱ¾ÍÒªÓõ½µÄSQLÖеÄcase¹Ø¼ü×Ö¡£ÈçÏÂËùʾ²éѯ¸ºÔðÈËÐÅÏ¢±í£¨T_MS_CustomerCrmInfoDutyPerson£©ÖеÄÊÇ·ñÊÇÖ÷Òª¸ºÔðÈË£¨isMainDuty£©×ֶΣ¬µ«ÊÇÕâ¸ö×ֶεÄÖµÊÇÒÔö¾ÙµÄ·½Ê½´æ´¢µÄ£¬´æ´¢0±íʾÊÇ·ñ£¬1±íʾÊÇ£¬·ñÔò±íʾδÅжϡ£Õâʱֱ½Ó²éѯ³öµÄÖµ²»ÊÇ×Ô¼ºÐèÒªµÄÖµ£¬¿ÉÒÔÓÃcase¹Ø¼ü×ÖÖØй¹½¨ÁÐÖµ¡£
select case
when cfdutystate=0 then '·ñ'
when cfdutystate=1 then 'ÊÇ'
else null
end as isMainDuty
from T_MS_CustomerCrmInfoDutyPerson
×¢Òâµã£º
1£¬ÒÔcase¿ªÍ·£¬ÒÔend½áβ
2£¬whenºó¸úÌõ¼þ·ÖÖ§ÖУ¬thenºóΪÏÔʾ½á¹û
3£¬elseΪ³ý´ËÖ®ÍâµÄĬÈÏÇé¿ö£¬ÀàËÆÓÚjavaµÄswitch caseµÄdefault£¬¿ÉÒÔ²»¼Ó
4£¬OracleÖÐendºó¸ú±ðÃû»òÕßsqlserverÖÐÓï·¨ÈçÏ£º
isMainDuty =case
when cfdutystate=0 then '·ñ'
when cfdutystate=1 then 'ÊÇ'
else null
end
³ý´ËÖ®Í⣬caseÒ²¿ÉÒÔÓõ½group byÓï¾äÖÐÒÔ¸ù¾ÝÐèҪʵÏÖÁé»îµÄ·Ö×é²Ù×÷£¬ÈçÏÂËùʾµÄÁÐ×Ó:
SELECT
CASE
WHEN price IS NULL THEN 'Unpriced'
WHEN price < 10 THEN 'Bargain'
WHEN price BETWEEN 10 and 20 THEN 'Average'
ELSE 'Gift to impress relatives'
&n
Ïà¹ØÎĵµ£º
²»Óð²×°Oracle ClientÈçºÎʹÓÃPLSQL Developer
1. ÏÂÔØoracleµÄ¿Í»§¶Ë³ÌÐò°ü£¨30M£©
Ö»ÐèÒªÔÚOracleÏÂÔØÒ»¸ö½ÐInstant Client PackageµÄÈí¼þ¾Í¿ÉÒÔÁË£¬Õâ¸öÈí¼þ²»ÐèÒª°²×°£¬Ö»Òª½âѹ¾Í¿ÉÒÔÓÃÁË£¬ºÜ·½±ã£¬¾ÍËãÖØ×°ÁËϵͳ»¹ÊÇ¿ÉÒÔÓõġ£
ÏÂÔص ......
µ¼Èë IMP
Oracle µÄµ¼ÈëʵÓóÌÐò (Import utility) ÔÊÐí´ÓÊý¾Ý¿âÌáÈ¡Êý¾Ý£¬²¢ÇÒ½«Êý¾ÝдÈë²Ù×÷ϵͳÎļþ¡£ imp ʹÓõĻù±¾¸ñʽ£º imp[username[/password[@service]]] £¬ÒÔÏÂÀý¾Ù imp ³£ÓÃÓ÷¨¡£
1. »ñÈ¡°ïÖú
imp help=y
2. µ¼ÈëÒ»¸öÍêÕûÊý¾Ý¿â
imp system/manager file=bible_db log=dibl ......
ÓÉÓÚ֮ǰѧϰoracleµÄʱºò±È½ÏËæÒ⣬¿´ÊéÒ²ÊDZȽÏÉÙÈ¥ÉîÈë˼¿¼ÔÀí£¬¶ø´ÓÇ°¼¸Ìì¾ö¶¨ÒªÖØÐÂȼÆðoracleµÄѧϰ¼¤Çéºó£¬ÎÒÓÖÖØеĴӻù´¡Ñ§Æ𣬴ËʱÎÒѧoracleµÄÐÄ̬Ҳ±ÈÒÔÇ°ºÃºÜ¶àÁË£¬ÏÖÔÚÊÇŬÁ¦µÄÒ»µãÒ»µãÈ¥¿ÐÁË£¬¼´Ê¹ºÜ»¨Ê±¼ä£¬µ«ÎÒÏëÕýÊÇͨ¹ý½ñÌìÎÒÕâÑùµÄÂñÍ·¿à¶Á²Å»á»»À´Óг¯Ò»ÈÕÔÚoracle·½ÃæµÄÖʵķÉÔ¾µÄ¡£
  ......
ʲôÊǺϲ¢¶àÐÐ×Ö·û´®£¨Á¬½Ó×Ö·û´®£©ÄØ£¬ÀýÈ磺
SQL> desc test;
Name Type Nullable Default Comments
------- ------------ -------- ------- --------
COUNTRY VARCHAR2(20) Y
CITY VARCHAR2(20) Y
SQL> select * from test;
COUNTRY CITY
-------------------- --------------------
Öйú ̨±±
Öйú Ïã ......
»ù±¾µÄSql±àдעÒâÊÂÏî
¾¡Á¿ÉÙÓÃIN²Ù×÷·û£¬»ù±¾ÉÏËùÓеÄIN²Ù×÷·û¶¼¿ÉÒÔÓÃEXISTS´úÌæ¡£
²»ÓÃNOT IN²Ù×÷·û£¬¿ÉÒÔÓÃNOT EXISTS»òÕßÍâÁ¬½Ó+Ìæ´ú¡£
OracleÔÚÖ´ÐÐIN×Ó²éѯʱ£¬Ê×ÏÈÖ´ÐÐ×Ó²éѯ£¬½«²éѯ½á¹û·ÅÈëÁÙʱ±íÔÙÖ´ÐÐÖ÷²éѯ¡£¶øEXISTÔòÊÇÊ×Ïȼì²éÖ÷²éѯ£¬È»ºóÔËÐÐ×Ó²éѯֱµ½ÕÒµ½
µÚÒ»¸öÆ¥ÅäÏî¡£NOT EXISTS±ÈNOT INЧÂÊÉ ......