SQLÖÐCaseµÄʹÓ÷½·¨(ÉÏƪ)
Case¾ßÓÐÁ½ÖÖ¸ñʽ¡£¼òµ¥Caseº¯ÊýºÍCaseËÑË÷º¯Êý¡£
--¼òµ¥Caseº¯Êý
CASE sex
WHEN '1' THEN 'ÄÐ'
WHEN '2' THEN 'Å®'
ELSE 'ÆäËû' END
--CaseËÑË÷º¯Êý
CASE WHEN sex = '1' THEN 'ÄÐ'
WHEN sex = '2' THEN 'Å®'
ELSE 'ÆäËû' END
ÕâÁ½ÖÖ·½Ê½£¬¿ÉÒÔʵÏÖÏàͬµÄ¹¦ÄÜ¡£¼òµ¥Caseº¯ÊýµÄд·¨Ïà¶Ô±È½Ï¼ò½à£¬µ«ÊǺÍCaseËÑË÷º¯ÊýÏà±È£¬¹¦ÄÜ·½Ãæ»áÓÐЩÏÞÖÆ£¬±ÈÈçдÅжÏʽ¡£
»¹ÓÐÒ»¸öÐèҪעÒâµÄÎÊÌ⣬Caseº¯ÊýÖ»·µ»ØµÚÒ»¸ö·ûºÏÌõ¼þµÄÖµ£¬Ê£ÏµÄCase²¿·Ö½«»á±»×Ô¶¯ºöÂÔ¡£
--±ÈÈç˵£¬ÏÂÃæÕâ¶ÎSQL£¬ÄãÓÀÔ¶ÎÞ·¨µÃµ½“µÚ¶þÀà”Õâ¸ö½á¹û
CASE WHEN col_1 IN ( 'a', 'b') THEN 'µÚÒ»Àà'
WHEN col_1 IN ('a') THEN 'µÚ¶þÀà'
ELSE'ÆäËû' END
ÏÂÃæÎÒÃÇÀ´¿´Ò»Ï£¬Ê¹ÓÃCaseº¯Êý¶¼ÄÜ×öЩʲôÊÂÇé¡£
Ò»£¬ÒÑÖªÊý¾Ý°´ÕÕÁíÍâÒ»ÖÖ·½Ê½½øÐзÖ×飬·ÖÎö¡£
ÓÐÈçÏÂÊý¾Ý:(ΪÁË¿´µÃ¸üÇå³þ£¬ÎÒ²¢Ã»ÓÐʹÓùú¼Ò´úÂ룬¶øÊÇÖ±½ÓÓùú¼ÒÃû×÷ΪPrimary Key)
¹ú¼Ò£¨country£©
ÈË¿Ú£¨population£©
Öйú
600
ÃÀ¹ú
100
¼ÓÄôó
100
Ó¢¹ú
200
·¨¹ú
300
ÈÕ±¾
250
µÂ¹ú
200
Ä«Î÷¸ç
50
Ó¡¶È
250
¸ù¾ÝÕâ¸ö¹ú¼ÒÈË¿ÚÊý¾Ý£¬Í³¼ÆÑÇÖ޺ͱ±ÃÀÖÞµÄÈË¿ÚÊýÁ¿¡£Ó¦¸ÃµÃµ½ÏÂÃæÕâ¸ö½á¹û¡£
ÖÞ
ÈË¿Ú
ÑÇÖÞ
1100
±±ÃÀÖÞ
250
ÆäËû
700
ÏëÒª½â¾öÕâ¸öÎÊÌ⣬Äã»áÔõô×ö£¿Éú³ÉÒ»¸ö´øÓÐÖÞCodeµÄView£¬ÊÇÒ»¸ö½â¾ö·½·¨£¬µ«ÊÇÕâÑùºÜÄѶ¯Ì¬µÄ¸Ä±äͳ¼ÆµÄ·½Ê½¡£
Èç¹ûʹÓÃCaseº¯Êý£¬SQL´úÂëÈçÏÂ:
SELECT SUM(population),
CASE country
WHEN 'Öйú' THEN 'ÑÇÖÞ'
WHEN 'Ó¡¶È' THEN 'ÑÇÖÞ'
WHEN 'ÈÕ±¾' THEN 'ÑÇÖÞ'
WHEN 'ÃÀ¹ú' THEN '±±ÃÀÖÞ'
WHEN '¼ÓÄôó' THEN '±±ÃÀÖÞ'
WHEN 'Ä«Î÷¸ç' THEN '±±ÃÀÖÞ'
ELSE 'ÆäËû' END
from Table_A
GROUP BY CASE country
WHEN 'Öйú' THEN 'ÑÇÖÞ'
WHEN 'Ó¡¶È' THEN 'ÑÇÖÞ'
WHEN 'ÈÕ±¾' THEN 'ÑÇÖÞ'
WHEN 'ÃÀ¹ú' THEN '±±ÃÀÖÞ'
WHEN '¼ÓÄôó' THEN '±±ÃÀÖÞ'
WHEN 'Ä«Î÷¸ç' THEN '±±ÃÀÖÞ'
ELSE 'ÆäËû' END;
ͬÑùµÄ£¬ÎÒÃÇÒ²¿ÉÒÔÓÃÕâ¸ö·½·¨À´ÅжϹ¤×ʵĵȼ¶£¬²¢Í³¼ÆÿһµÈ¼¶µÄÈËÊý¡£SQL´úÂëÈçÏ£»
Ïà¹ØÎĵµ£º
USE StudentInfo
--=====================================================
--Author £ºyangjuncheng
--Create Date:2010.5.26
--Decription :¸ø±íÌí¼ÓÔ¼Êø£¨¿ÉÒÔÔÚ´´½¨±íʾֱ½ÓÌí¼Ó
-- Ò²¿ÉÒÔʹÓÃalter¹Ø¼ ......
Ò»¡¢·Öҳд·¨Ð¡Àý£º
SELECT FIRST 10 templateid,code,name from template ;
SELECT FIRST 10 SKIP 10 templateid,code,name from template ;
SELECT * from shop ROWS 1 TO 10; –firebird2.0Ö§³ÖÕâÖÖд·¨
¶þ¡¢ÏÔʾ±íÃûºÍ±í½á¹¹
SHOW TABLES;
SHOW TABLE tablename;
ËÄ¡¢¸üÐÂ×Ö¶Î×¢ÊÍ
......
Ç°ÑÔ
×î½ü½ÓÁ¬Óöµ½¼¸¸öÅóÓÑÎÊÎÒͬһ¸öÎÊÌ⣬¾ÍÊǹØÓÚ.NETƽ̨ÉÏORM¿ò¼ÜµÄÑ¡Ôñ¡£ÎÒÏëÔÚÕâ¸ö½²ÇóЧÂʵÄʱ´ú£¬ËÒ²²»ÏëÊÖдSQL»ò´æ´¢¹ý³ÌÈ¥·ÃÎÊÊý¾Ý¿âÁË¡£´ó¼Ò¶¼ÖªµÀ£¬ÔÚJavaƽ̨ÉÏ£¬ORMÕâÒ»¿é»ù±¾ÊÇHibernateµÄÌìÏ¡£µ±È»£¬Ïà¶ÔÇáÁ¿¼¶µÄiBatisÒ²Óв»´íµÄ±íÏÖ¡£
&nb ......
ÀàÐÍÃû³Æ
Oracle
SQLServer
±È½Ï
×Ö·ûÊý¾ÝÀàÐÍ
CHAR
CHAR
¶¼Êǹ̶¨³¤¶È×Ö·û×ÊÁϵ«oracleÀïÃæ×î´ó¶ÈΪ2kb£¬SQLServerÀïÃæ×î´ó³¤¶ÈΪ8kb
±ä³¤×Ö·ûÊý¾ÝÀàÐÍ
VARCHAR2
VARCHAR
OracleÀïÃæ×î´ó³¤¶ÈΪ4kb£¬SQLServerÀïÃæ×î´ó³¤¶ÈΪ8kb
¸ù¾Ý×Ö·û¼¯¶ø¶¨µÄ¹Ì¶¨³¤¶È×Ö·û´®
NCHAR
NCHAR
Ç°Õß×î´ó³¤¶È2kbºóÕß×î´ó³¤¶È4 ......
×î½üÔÚÒ»¸öÏîÄ¿ÖÐÓöµ½ÐèÒªÔÚÊý¾Ý²ã¾ÍÆ´½Ó±íÖÐÒ»ÁÐÊý¾ÝµÄÎÊÌâ¡£
ÀýÈ磬test±íÖÐÓиö×Ö¶Ît,tÁÐÖеÄ4ÐÐÊý¾ÝΪ1,2,3,4 £¬ÒªÆ´½Ó³É1+2+3+4£¬×ÁÄ¥ÁËÒ»Õ󣬱¾À´ÏëÓÃÓα꣬µ«ÊÇЧÂÊ¡£¡£ºóÀ´ÕÒµ½Ò»¶ÎSQL£¬¿ÉÒԺܷ½±ãµØÆ´½Ó¡£
DECLARE @STR VARCHAR(8000) ----¶¨Òå²éѯ×Ö·û´®
SELECT @STR=ISNULL(@STR+'+','')+t from (SELECT DIST ......