sqlÓïÑÔÖеÄcase when £¨À´×ÔÎÒ×ð¾´µÄһλÀÏʦСF£©
sqlÓïÑÔÖÐÓÐûÓÐÀàËÆCÓïÑÔÖеÄswitch caseµÄÓï¾ä£¿£¿
ûÓÐ,ÓÃcase when À´´úÌæ¾ÍÐÐÁË.
ÀýÈç,ÏÂÃæµÄÓï¾äÏÔʾÖÐÎÄÄêÔÂ
select getdate() as ÈÕÆÚ,case month(getdate())
when 11 then 'ʮһ'
when 12 then 'Ê®¶þ'
else substring('Ò»¶þÈýËÄÎåÁùÆß°Ë¾ÅÊ®', month(getdate()),1)
end+'ÔÂ' as Ô·Ý
=====================================================================
CASE ¿ÉÄÜÊÇ SQL Öб»ÎóÓÃ×î¶àµÄ¹Ø¼ü×ÖÖ®Ò»¡£ËäÈ»Äã¿ÉÄÜÒÔǰÓùýÕâ¸ö¹Ø¼ü×ÖÀ´´´½¨×ֶΣ¬µ«ÊÇËü»¹¾ßÓиü¶àÓ÷¨¡£ÀýÈ磬Äã¿ÉÒÔÔÚ WHERE ×Ó¾äÖÐʹÓà CASE¡£
Ê×ÏÈÈÃÎÒÃÇ¿´Ò»Ï CASE µÄÓï·¨¡£ÔÚÒ»°ãµÄ SELECT ÖУ¬ÆäÓï·¨ÈçÏ£º
SELECT <myColumnSpec> =
CASE
WHEN <A> THEN <somethingA>
WHEN <B> THEN <somethingB>
ELSE <somethingE>
END
ÔÚÉÏÃæµÄ´úÂëÖÐÐèÒªÓþßÌåµÄ²ÎÊý´úÌæ¼âÀ¨ºÅÖеÄÄÚÈÝ¡£ÏÂÃæÊÇÒ»¸ö¼òµ¥µÄÀý×Ó£º
USE pubs
GO
SELECT
Title,
'Price Range' =
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'
END
from titles
ORDER BY price
GO
ÕâÊÇ CASE µÄµäÐÍÓ÷¨£¬µ«ÊÇʹÓà CASE Æäʵ¿ÉÒÔ×ö¸ü¶àµÄÊÂÇé¡£±È·½ËµÏÂÃæµÄ GROUP BY ×Ó¾äÖÐµÄ CASE£º
SELECT 'Number of Titles', Count(*)
from titles
GROUP BY
CASE
WHEN price IS NULL THEN 'Unpriced'
&n
Ïà¹ØÎĵµ£º
create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int output,--ÿҳÏÔʾ¼Ç¼ÌõÊý
@currentpage int output,--µÚ¼¸Ò³
@orderid nvarchar(50),--Ö÷¼üÅÅÐò
@sort int,--ÅÅÐò·½Ê½£¬1±íʾÉýÐò£¬0±íʾ½µÐòÅÅÁÐ
......
showºÍsetÃüÁîÊÇÁ½ÌõÓÃÓÚά»¤SQL*Plusϵͳ±äÁ¿µÄÃüÁî
SQL> show all --²é¿´ËùÓÐ68¸öϵͳ±äÁ¿Öµ
SQL> show user --ÏÔʾµ±Ç°Á¬½ÓÓû§
SQL> show error¡¡¡¡ --ÏÔʾ´íÎó
SQL> set heading off --½ûÖ¹Êä³öÁбêÌ⣬ĬÈÏֵΪON
SQL> set feedback off --½ûÖ¹ÏÔʾ×îºóÒ»ÐеļÆÊý·´À¡ÐÅÏ¢£¬Ä¬ÈÏֵΪ"¶Ô6¸ö» ......
Ê×ÏȼÆËã»úÉϱØÐë°²×°oracle£¬²¢ÅäÖú÷þÎñÃû
°²×°oracleµÄ¹ý³ÌÖÐÒ»°ã»áÒªÇó½¨Á¢Ò»¸öÊý¾Ý¿â£¬Ò²¿ÉÒÔÖ®ºóÔÙ´´½¨
Õâ¸öºóÃæÓÐÒ»¸ö Êý¾Ý¿âÅäÖÃÖúÀí £¨´´½¨ÐÞ¸ÄÊý¾Ý¿âµÄ£¬Ò»°ãÃÜÂë´´½¨ÒªÇó×Öĸ¿ªÍ·£¬´óÓÚ7λɶµÄ£©ºÍÒ»¸önetÅäÖÃÖúÀí£¨Ìí¼Ó»òÕßÐÞ¸ÄÒ»¸öÊý¾Ý¿â·þÎñÃû£©£¬Ò»°ãÓÃplsql developerÔ¶³Ì²Ù×÷oracleÊý¾Ý¿â ......
²éѯËÙ¶ÈÂýµÄÔÒòºÜ¶à£¬³£¼ûÈçϼ¸ÖÖ£º
1¡¢Ã»ÓÐË÷Òý»òÕßûÓÐÓõ½Ë÷Òý(ÕâÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)
2¡¢I/OÍÌÍÂÁ¿Ð¡£¬ÐγÉÁËÆ¿¾±Ð§Ó¦¡£
3¡¢Ã»Óд´½¨¼ÆËãÁе¼Ö²éѯ²»ÓÅ»¯¡£
4¡¢ÄÚ´æ²»×ã
5¡¢ÍøÂçËÙ¶ÈÂý
6¡¢²éѯ³öµÄÊý¾ÝÁ¿¹ý´ó£¨¿ÉÒÔ²ÉÓöà´Î²éѯ£¬ÆäËûµÄ·½·¨½µµÍÊý¾ÝÁ¿£©
7¡¢Ëø»òÕßËÀËø(ÕâÒ²ÊDzéѯÂý×î³ ......
--SQL ËÙ²éÊÖ²á
/*******************************************/
SELECT
--ÓÃ;£º´ÓÖ¸¶¨±íÖÐÈ¡³öÖ¸¶¨ÁеÄÊý¾Ý
--Óï·¨£º
SELECT column_name(s) from table_name
--Ö÷Òª×Ö¾ä¿ÉժҪΪ:
SELECT select_list [INTO new_table]
from table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING s ......