CASEÔÚsql serverÖеÄʹÓÃÓ÷¨
CASE Óï¾äÔÚsql server¸úÆäËü³ÌÐòÓïÑÔÖеÄswitch¹¦ÄÜÀàËÆ£¬ÓÃÓÚ¼ÆËãÌõ¼þÁÐ±í²¢·µ»Ø¶à¸ö¿ÉÄܽá¹û±í´ïʽ֮һ¡£
ÔÚsql serverÖÐCASE¾ßÓÐÁ½ÖÖ¸ñʽ£º
a.¼òµ¥ CASE º¯Êý½«Ä³¸ö±í´ïʽÓëÒ»×é¼òµ¥±í´ïʽ½øÐбȽÏÒÔÈ·¶¨½á¹û¡£
b.CASE ËÑË÷º¯Êý¼ÆËãÒ»×é²¼¶û±í´ïʽÒÔÈ·¶¨½á¹û¡£
ÒÔÉÏÁ½ÖÖ¸ñʽ¶¼Ö§³Ö¿ÉÑ¡µÄ ELSE ²ÎÊý¡£
³£¼ûµÄ¼¸ÖÖCASEÓï¾äµÄÓ÷¨ÈçÏÂËùʾ:
1.CASE º¯ÊýÓÃÓÚ¼ÆËã¶à¸öÌõ¼þ²¢ÎªÃ¿¸öÌõ¼þ·µ»Øµ¥¸öÖµ¡£CASE º¯Êýͨ³£µÄÓÃ;ÊÇʹÓÿɶÁÐÔ¸üÇ¿µÄÖµÌæ»»´úÂë»òËõд¡£
ÏÂÃæµÄ²éѯʹÓà CASE º¯ÊýÖØÃüÃûÊé¼®µÄ·ÖÀ࣬ÒÔʹ֮¸üÒ×Àí½â¡£
USE pubs
SELECT
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END AS Category,
CONVERT(varchar(30), title) AS "Shortened Title",
price AS Price
from titles
WHERE price IS NOT NULL
ORDER BY 1
2.ʹÓôøÓмòµ¥ CASE º¯ÊýºÍ CASE ËÑË÷º¯ÊýµÄ SELECT Óï¾ä
CASE º¯ÊýµÄÁíÒ»¸öÓÃ;¸øÊý¾Ý·ÖÀà¡£ÏÂÃæµÄ²éѯʹÓà CASE º¯Êý¶Ô¼Û¸ñ·ÖÀà¡£
SELECT
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END AS "Price Category",
CONVERT(varchar(20), title) AS "Shortened Title"
from pubs.dbo.titles
ORDER BY price
3.ʹÓôøÓÐ SUBSTRING ºÍ SELECT µÄ CASE º¯Êý
ÏÂÃæµÄʾÀýʹÓà CASE ºÍ THEN Éú³ÉÒ»¸öÓйØ×÷Õß¡¢Í¼Êé±êʶºÅºÍÿ¸ö×÷ÕßËùÖøͼÊéÀàÐ͵ÄÁÐ±í¡£
USE pubs
SELECT SUBSTRING((RTRIM(a.au_fname) + ' '+
RTRIM(a.au_lname) + ' '), 1, 25) AS Name, a
Ïà¹ØÎĵµ£º
character-set-server = GB2312
collation-server = latin1_general_ci
MySQL×Ö·û¼¯ GBK¡¢GB2312¡¢UTF8Çø±ð ½â¾ö MYSQLÖÐÎÄÂÒÂëÎÊÌâ ÊÕ²Ø
MySQLÖÐÉæ¼°µÄ¼¸¸ö×Ö·û¼¯
character-set-server/default-character-set£º·þÎñÆ÷×Ö·û¼¯£¬Ä¬ÈÏÇé¿öÏÂËù²ÉÓõġ£
character-set-database£ºÊý¾Ý¿â×Ö·û¼¯¡£
character-set-table£ºÊ ......
Sql´úÂë
--²ÉÓÃSQLÓï¾äʵÏÖsql2005ºÍExcel Êý¾ÝÖ®¼äµÄÊý¾Ýµ¼Èëµ¼³ö£¬ÔÚÍøÉÏÕÒÀ´Ò»--Ï£¬ÊµÏÖ·½·¨ÊÇÕâÑùµÄ£º
--Excel---->SQL2005 µ¼È룺
select * into useinfo from O ......
ÔÚ±¾ÎÄÖУ¬´ËʾÀý±ê×¼À¶Í¼µÄ´æ´¢¹ý³ÌÃüÃû·½·¨Ö»ÊÊÓÃÓÚSQLÄÚ²¿£¬¼ÙÈçÄãÕýÔÚ´´½¨Ò»¸öеĴ洢¹ý³Ì£¬»òÊÇ·¢ÏÖÒ»¸öûÓа´ÕÕÕâ¸ö±ê×¼¹¹ÔìµÄ´æ´¢¹ý³Ì£¬¼´¿ÉÒԲο¼Ê¹ÓÃÕâ¸ö±ê×¼¡£
×¢ÊÍ£º¼ÙÈç´æ´¢¹ý³ÌÒÔsp_ Ϊǰ׺¿ªÊ¼ÃüÃûÄÇô»áÔËÐеÄÉÔ΢µÄ»ºÂý£¬ÕâÊÇÒòΪSQL Server½«Ê×ÏȲéÕÒϵͳ´æ´¢¹ý³Ì£¬ËùÒÔÎÒÃǾö²»ÍƼöʹÓÃsp_×÷Ϊǰ׺¡ ......
DBCC DROPCLEANBUFFERS --Çå³ý»º³åÇø£¬±ãÓڶԱȲéѯʱ¼äºÍÐÔÄÜ£¬²»ÊÕ»º´æµÄÓ°Ïì
SET STATISTICS TIME ON --ÏÔʾ·ÖÎö¡¢±àÒëºÍÖ´Ðи÷Óï¾äËùÐèµÄºÁÃëÊý
sp_spaceused NSDoctorAdvice0705 -- ²é¿´±í¿Õ¼ä´óС
ÎÊÌâÌÖÂÛ£º
ÊÇ·ñÔö¼ÓÒ»¸ö¶ÀÁ¢µÄÎļþ×飬ÓÃÀ´´æ·ÅË÷Òý¡£Ä¿Ç°Êý¾Ý¿âµÄÖ»ÓÐÒ»¸öÎļþ×飬Îļþ·Ç³£´óµÄ»°£¬ ......