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
Ïà¹ØÎĵµ£º
ÎÒÃÇ¿ÉÒÔͨ¹ýSTART WITH . . . CONNECT BY . . .×Ó¾äÀ´ÊµÏÖSQLµÄ ²ã´Î²éѯ£¬¶øOracle 10g ΪÆäÌí¼ÓÐí¶àÁËеÄαÁС£Ê®¶àÄêÒÔÀ´£¬Oracle SQL ¾ßÓÐÒÀÕÕ²ã´Î¹Øϵ½øÐвéѯµÄ¹¦ÄÜ¡£ÀýÈ磬Äã¿ÉÒÔÖ¸¶¨Ò»¸öÆðʼÌõ¼þ£¬È»ºó¸ù¾ÝÒ»¸ö»ò¶à¸öÁ¬½ÓÌõ¼þÀ´È·¶¨º¢×ÓÐеÄÄÚÈÝ¡£¾ÙÀýÀ´Ëµ£¬ÏÖÔÚ¼ÙÉèÎÒÓÐÒ»¸ö±í£¬ÀïÃæ¼Ç ......
truncate,delete,dropµÄÒìͬµã
×¢Òâ:ÕâÀï˵µÄdeleteÊÇÖ¸²»´øwhere×Ó¾äµÄdeleteÓï¾ä
Ïàͬµã:truncateºÍ²»´øwhere×Ó¾äµÄdelete, ÒÔ¼°drop¶¼»áɾ³ý±íÄÚµÄÊý¾Ý
²»Í¬µã:
1. truncateºÍ deleteֻɾ³ýÊý¾Ý²»É¾³ý±íµÄ½á¹¹(¶¨Òå)
dropÓï¾ä½«É¾³ý±íµÄ½á¹¹±»ÒÀÀµµÄÔ¼Êø(constrain),´¥·¢Æ÷(trigger),Ë÷Òý(index); ÒÀÀµÓÚ¸Ã±íµ ......
ͨ¹ý select * from table whereid=16701 for update ËøסһÕűí
ͨ¹ýÒÔÏÂÓï¾ä¿É²éѯ³ö±»ËøסµÄ¶ÔÏó
SELECT OBJECT_ID,
SESSION_ID,
SERIAL#,
ORACLE_USERNAME,
&nb ......
2010-04-21 14:04
oracleÖй¹ÔìÊý×éµÄÀý×Ó£º
declare
type t_varray is varray(4) of number;
arr t_varray;
begin
arr := t_varray(1,2,3,4);
for i in 1..arr.count loop
dbms_output.put_line(arr(i));
end loop;
end;
¹¹Ôì¶þάÊý×éµÄÀý×Ó£º
declare
type t_varray ......