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£¬Äã¿ÉÄÜ»áÓöµ½Oracle tnsÅäÖÃÎÊÌ⣬ÕâÀォ½éÉÜOracle tnsÅäÖÃÎÊÌâµÄ½â¾ö·½·¨¡£×î½üæ×Å°²×°OracleÊý¾Ý¿â£¬±¾À´Í¦¼òµ¥µÄ£¬¿ÉÀÏÊdzöÏÖÎÊÌ⣬×îºó×Ô¼ºÔÚÍøÉÏÕûÀíÁËһЩtns´íÎó½â¾ö·½·¨£¬Ï£Íû¶Ô³õѧÕßÓÐÒæ¡£
³£¼ûÎÊÌâ:
1¡¢ORA-12541:tns:ûÓмàÌýÆ÷£º
ÏÔ¶øÒ×¼û£¬·þÎñÆ÷¶ËµÄ¼àÌýÆ÷ûÓÐÆô¶¯£¬ÁíÍâ¼ì²é¿Í» ......
Oracle Êý¾ÝÀàÐͼ°´æ´¢·½Ê½
Ô¬¹â¶« Ô´´
¸ÅÊö
ͨ¹ýʵÀý£¬È«Ãæ¶øÉîÈëµÄ·ÖÎöoralceµÄ»ù±¾Êý¾ÝÀàÐͼ°ËüÃǵĴ洢·½Ê½¡£ÒÔORACLE 10GΪ»ù´¡£¬½éÉÜoralce
10gÒýÈëµÄеÄÊý¾ÝÀàÐÍ¡£ÈÃÄã¶Ôor ......
OracleµÄÈÕÆÚº¯Êý
³£ÓÃÈÕÆÚÐͺ¯Êý
1¡£Sysdate µ±Ç°ÈÕÆÚºÍʱ¼ä
SQL> Select sysdate from dual;
SYSDATE
----------
21-6ÔÂ -05
2¡£Last_day ±¾ÔÂ×îºóÒ»Ìì
SQL> Select last_day(sysdate) from dual;
LAST_DAY(S ......
select * from TTable1 for update Ëø¶¨±íµÄËùÓÐÐУ¬Ö»ÄܶÁ²»ÄÜд
2 select * from TTable1 where pkid = 1 for update Ö»Ëø¶¨pkid=1µÄÐÐ
3 select * from Table1 a join Table2 b on a.pkid=b.pkid for update Ëø¶¨Á½¸ö±íµÄËùÓмǼ
4 select * from Table1 a join Table2 b on a.pki ......
ÓÉÓÚ֮ǰѧϰoracleµÄʱºò±È½ÏËæÒ⣬¿´ÊéÒ²ÊDZȽÏÉÙÈ¥ÉîÈë˼¿¼ÔÀí£¬¶ø´ÓÇ°¼¸Ìì¾ö¶¨ÒªÖØÐÂȼÆðoracleµÄѧϰ¼¤Çéºó£¬ÎÒÓÖÖØеĴӻù´¡Ñ§Æ𣬴ËʱÎÒѧoracleµÄÐÄ̬Ҳ±ÈÒÔÇ°ºÃºÜ¶àÁË£¬ÏÖÔÚÊÇŬÁ¦µÄÒ»µãÒ»µãÈ¥¿ÐÁË£¬¼´Ê¹ºÜ»¨Ê±¼ä£¬µ«ÎÒÏëÕýÊÇͨ¹ý½ñÌìÎÒÕâÑùµÄÂñÍ·¿à¶Á²Å»á»»À´Óг¯Ò»ÈÕÔÚoracle·½ÃæµÄÖʵķÉÔ¾µÄ¡£
  ......