SQLÖÐcase whenµÄÁ½ÖÖʹÓ÷½·¨Ê¾Àý
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 '
Ïà¹ØÎĵµ£º
use myoa
select * from
delete from department where departmentid=1
insert Department(DepartmentId,DepartmentName) values(1,'¼¼Êõ²¿')
update Department set departmentname='ÐÅÏ¢¼¼Êõ²¿' where departmentid=1
--ɾ³ý±í
drop table department
--ɾ³ýÊý¾Ý¿â
drop database bai
--ͳ¼ÆÊý¾Ý¿â±íÖмǼ
se ......
1. GROUP BY ÊÇ·Ö×é²éѯ, Ò»°ã GROUP BY ÊǺ;ۺϺ¯ÊýÅäºÏʹÓÃ
group by ÓÐÒ»¸öÔÔò,¾ÍÊÇ select ºóÃæµÄËùÓÐÁÐÖÐ,ûÓÐʹÓþۺϺ¯ÊýµÄÁÐ,±ØÐë³öÏÖÔÚ group by ºóÃ棨ÖØÒª£©
ÀýÈç,ÓÐÈçÏÂÊý¾Ý¿â±í£º
A B
1 abc
1 bcd
1 asdfg
Èç¹ûÓÐÈ ......
±¾ÎÄÖ÷Òª½²ÊöÈý¸öÄÚÈÝ£º
1.ÈçºÎ´´½¨hierarychyidµÄ±í£¬²åÈëÊý¾Ý¼°»ù±¾µÝ¹é²éѯ¡£
2.½éÉÜhierarchyidµÄ10ÖÖרÓк¯Êý¡£
3.½éÉÜhierarchyidÌØÓеÄÉî¶ÈÓÅÏÈË÷Òý(Depth-First Indexing)ºÍ¹ã¶ÈÓÅÏÈË÷Òý(Breadth-First Indexing)
ÔÚÉÏÒ»½ÚÖÐ
http://blog.csdn.net/tjvictor/archive/2009/07/30/4395677.aspx
ÎÒÃÇÒѾÑÝ ......
"SQL Server °²×°³ÌÐòÎÞ·¨»ñÈ¡ ASPNET ÕÊ»§µÄϵͳÕÊ»§ÐÅÏ¢"
½â¾ö°ì·¨£º
ÓÃaspnet_regiisʵÓù¤¾ßжÔغÍÖØа²×°Ò»Ï¾ͿÉÒÔÁË¡£
¾ßÌåµÄ²Ù×÷£º
1¡¢½øÈëCMD£º
C:\windows\microsoft.net\framework\v2.0.50727Îļþ¼ÐÏ£¬ÔËÐÐaspnet_regiis -uжÔØ
È»ºóÔËÐÐaspnet_regiis -i ÖØа²×°£¬ÉÏÊöÎÊÌâ¼´¿É½â¾ö¡£
2¡¢C:\WINDOWS\ ......
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
Go
----½ØÈ¡×Ö·û´®£¬²¢³öÈ¥html
create FUNCTION [dbo].[CutString] (@str varchar(1000),@length int)
RETURNS varchar(1000) AS
BEGIN
declare @mainstr varchar(1000)
declare @substr varchar(1000)
if(@str is not null or @st ......