SQLÖÐCASEʹÓÃ
½ñÌìÔÚÏîÄ¿ÖÐÓÐÒ»ÎÊÌ⣬ÔÚÍøÉϲéѯÁËcaseµÄÓ÷¨£¬Ìû³öÀ´ºÍ´ó¼Ò·ÖÏíÏ¡£
ÎÊÌâÃèÊö£ºÔÚÒ»ÕűíÖÐÓÐÒ»×Ö¶ÎbitÀàÐÍ£¬±íʾ´ËÌõÊý¾ÝÊÇ·ñ±»Ëø¶¨£¬ÔÚÒ³ÃæÉÏÓÐÒ»°´Å¥ÊǶԴËÌõÊý¾Ý½øÐÐËø¶¨ºÍ½âËøµÄ£¬Ñ¡ÔñÒ³ÃæÖеÄÊý¾Ý£¬µã»÷Õâ¸ö°´Å¥£¬Èç¹ûÕâÌõÊý¾ÝÊÇËø¶¨µÄ£¬¾Í½âËø£»Èç¹ûÊÇδ˵¶¨µÄ¾ÍËø¶¨£¬ÕâÑù¾ÍÓÃÒ»ÌõÓï¾äÀ´ÊµÏÖ¡£ºóÀ´Ïëµ½ÒÔǰʹÓùýsqlÖеÄcaseÓï¾äÄÜʵÏÖÕâÖÖ¹¦ÄÜ¡£²éѯÁËһϣ¬¹ûÈ»ºÃÓ㬺ǺÇ.....
CASE
¼ÆËãÌõ¼þÁÐ±í²¢·µ»Ø¶à¸ö¿ÉÄܽá¹û±í´ïʽ֮һ¡£
CASE ¾ßÓÐÁ½ÖÖ¸ñʽ£º
¼òµ¥ CASE º¯Êý½«Ä³¸ö±í´ïʽÓëÒ»×é¼òµ¥±í´ïʽ½øÐбȽÏÒÔÈ·¶¨½á¹û¡£
CASE ËÑË÷º¯Êý¼ÆËãÒ»×é²¼¶û±í´ïʽÒÔÈ·¶¨½á¹û¡£
Á½ÖÖ¸ñʽ¶¼Ö§³Ö¿ÉÑ¡µÄ ELSE ²ÎÊý¡£
Óï·¨
¼òµ¥ CASE º¯Êý£º
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
END
CASE ËÑË÷º¯Êý£º
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
END
²ÎÊý
input_expression
ÊÇʹÓüòµ¥ CASE ¸ñʽʱËù¼ÆËãµÄ±í´ïʽ¡£Input_expression ÊÇÈκÎÓÐЧµÄ Microsoft? SQL Server? ±í´ïʽ¡£
WHEN when_expression
ʹÓüòµ¥ CASE ¸ñʽʱ input_expression Ëù±È½ÏµÄ¼òµ¥±í´ïʽ¡£When_expression ÊÇÈÎÒâÓÐЧµÄ SQL Server ±í´ïʽ¡£Input_expression ºÍÿ¸ö when_expression µÄÊý¾ÝÀàÐͱØÐëÏàͬ£¬»òÕßÊÇÒþÐÔת»»¡£
n
ռλ·û£¬±íÃ÷¿ÉÒÔʹÓöà¸ö WHEN when_expression THEN result_expression ×Ó¾ä»ò WHEN Boolean_expression THEN result_expression ×Ӿ䡣
THEN result_expression
µ± input_expression = when_expression ȡֵΪ TRUE£¬»òÕß Boolean_expression ȡֵΪ TRUE ʱ·µ»ØµÄ±í´ïʽ¡£result expression ÊÇÈÎÒâÓÐЧµÄ SQL Server ±í´ïʽ¡£
ELSE else_result_expression
µ±±È½ÏÔËËãÈ¡Öµ²»Îª TRUE ʱ·µ»ØµÄ±í´ïʽ¡£Èç¹ûÊ¡ÂԴ˲ÎÊý²¢ÇұȽÏÔËËãÈ¡Öµ²»Îª TRUE£¬CASE ½«·µ»Ø NULL Öµ¡£Else_result_expression ÊÇÈÎÒâÓÐЧµÄ SQL Server ±í´ïʽ¡£Else_result_expression ºÍË
Ïà¹ØÎĵµ£º
˵¾ä´ó·Ï»°£¬ÄǾÍÊÇ“ºÏÀíµÄƽºâ¸÷ÖÖ×ÊÔ´µÄʹÓÃ,ÄÚ´æ,cpu,io µÈµÈ”¡£ÕâÒ²ÊǺÜÓеÀÀíµÄ£¬µ«ÊÇʵ¼Ê¾Í²»ºÃ×öÁË£¬ÈçºÎƽºâÄØ£¿
¾ßÌåµãµÄô£¬Í¨¹ý±È½Ï£¬Ó¦¸ÃÊÇÏìӦʱ¼ä×öΪÖ÷ÒªµÄÆÀÅÐÒòËØÁË¡£µ«ÊÇ¿ÉÄÜÓÉÓÚһЩ²»È·¶¨µÄÒòËØ£¬¿ÉÄÜ»á³öÏÖ£ºÕâ´ÎÅܵĿ죬²»µÈÓÚÏ´ÎÒ²ÅܵĿìÁË¡£
ÄǾÍÒª¸ù¾Ý±íµÄÊý¾ÝÁ¿µÄ±ä»¯£¬È·¶¨Ò»¸ö± ......
Select * from tableName
exec('select * from tableName')
exec sp_executesql N'select * from tableName' -- Çë×¢Òâ×Ö·û´®Ç°Ò»¶¨Òª¼ÓN
2:×Ö¶ÎÃû£¬±íÃû£¬Êý¾Ý¿âÃûÖ®Àà×÷Ϊ±äÁ¿Ê±£¬±ØÐëÓö¯Ì¬SQL
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- ´íÎó,²»»áÌáʾ´í ......
SQLÖÐCONVERTº¯Êý×î³£ÓõÄÊÇʹÓÃconvertת»¯³¤ÈÕÆÚΪ¶ÌÈÕÆÚ
Èç¹ûֻҪȡyyyy-mm-dd¸ñʽʱ¼ä, ¾Í¿ÉÒÔÓà convert(nvarchar(10),field,120)
120 ÊǸñʽ´úÂë, nvarchar(10) ÊÇָȡ³öÇ°10λ×Ö·û.
SELECT CONVERT(nvarchar(10), getdate(), 120)
SELECT CONVERT(varchar(10), getdate(), 120)
SELECT CONVERT(char(10), ge ......
×÷Õߣº sealyu ÈÕÆÚ£º2008-04-17
ÔÚSQL Server ÖУ¬Èç¹û¸ø±íµÄÒ»¸ö×Ö¶ÎÉèÖÃÁËĬÈÏÖµ£¬¾Í»áÔÚϵͳ±ísysobjectsÖÐÉú³ÉÒ»¸öĬÈÏÔ¼Êø¡£
Èç¹ûÏëɾ³ýÕâ¸öÉèÖÃÁËĬÈÏÖµµÄ×ֶΣ¨¼ÙÉè´Ë×Ö¶ÎÃûcolumn1£©£¬
Ö´ÐГALTER TABLE table1 DROP COLUMN column1”ʱ¾Í»á±¨´í£º
The object 'DF__xxxxxxxxxxx' ......