[MS SQL]SQL表达式CASE讲解

CASE 是 SQL 用來做為 if-then-else 之類邏輯的關鍵字。 CASE 的語法如下：

CASE is used to provide if-then-else type of logic to SQL.Its syntax is: 

SELECT CASE ("欄位名")
  WHEN "條件1" THEN "結果1"
  WHEN "條件2" THEN "結果2"
  ...
  [ELSE "結果N"]
  END
FROM "表格名"

"條件" 可以是一個數值或是公式。 ELSE 子句則並不是必須的。

"condition" can be a static value or an expression. The ELSE clause is optional.

在我們的 Store_Information 中 In our Table Store_Information example,

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
San Francisco	$300	Jan-08-1999
Boston	$700	Jan-08-1999

若我們要將 'Los Angeles' 的 Sales 數值乘以2，以及將 'San Diego' 的 Sales 數值乘以1.5，我們就鍵入以下的 SQL：

If we want to multiply the sales amount from  'Los Angeles' by 2 and the sales amount from  'San Diego' by 1.5, we key in   

SELECT store_name, CASE store_name
  WHEN 'Los Angeles' THEN Sales * 2
  WHEN 'San Diego' THEN Sales * 1.5
  ELSE Sales
  END
"New Sales",
Date
FROM Store_Information
"New Sales" 是用到 CASE 那個欄位的欄位名。

"New Sales" is the name given to the column with the CASE statement.

結果:

store_name	New Sales	Date
Los Angeles	$3000	Jan-05-1999
San Diego	$375	Jan-07-1999
San Francisco	$300	Jan-08-1999
Boston	$700	Jan-08-1999

http://msdn.microsoft.com/en-us/library/ms181765.aspx

1 简单的CASE用法

 USE AdventureWorks2008R2;

 GO 

 SELECT ProductNumber, Category =

 CASE ProductLine

 WHEN 'R' THEN 'Road' 

 WHEN 'M' THEN 'Mountain' 

 WHEN 'T' THEN 'Touring' 

 WHEN 'S' THEN 'Other sale items' 

 ELSE 'Not for sale' 

 END,

 Name

 FROM Production.Product

 ORDER BY ProductNumber;

 GO

2 稍微复杂的CASE用法

 USE AdventureWorks2008R2;

 GO 

 SELECT ProductNumber, Name, 'Price Range' =

 CASE 

 WHEN ListPrice = 0 THEN 'Mfg item - not for resale' 

 WHEN ListPrice < 50 THEN 'Under $50' 

 WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250' 

 WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000' 

 ELSE 'Over $1000' 

 END 

 FROM Production.Product

 ORDER BY ProductNumber ;

 GO

3 在order by中用CASE语句

 SELECT BusinessEntityID, SalariedFlag

 FROM HumanResources.Employee

 ORDER BY 

 CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC 

 ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;

 GO

另外一个例子

 SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName

 FROM Sales.vSalesPerson

 WHERE TerritoryName IS NOT NULL 

 ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName

 ELSE CountryRegionName END;

4 在UPDATE中用CASE语句

 USE AdventureWorks2008R2;

 GO 

 UPDATE HumanResources.Employee

 SET VacationHours =

 ( CASE 

 WHEN                           THEN VacationHours + 40

 ELSE (VacationHours + 20.00)

 END 

 )

 OUTPUT Deleted.BusinessEntityID, Deleted.VacationHours AS BeforeValue,

 Inserted.VacationHours AS AfterValue

 WHERE SalariedFlag = 0;

5 在set中用到CASE语句

 SET @ContactType =

 CASE 

 -- Check for employee 

 WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e

 WHERE e.BusinessEntityID = @BusinessEntityID)

 THEN 'Employee' 

 -- Check for vendor 

 WHEN EXISTS(SELECT * FROM Person.BusinessEntityContact AS bec

 WHERE bec.BusinessEntityID = @BusinessEntityID)

 THEN 'Vendor' 

 -- Check for store 

 WHEN EXISTS(SELECT * FROM Purchasing.Vendor AS v

 WHERE v.BusinessEntityID = @BusinessEntityID)

 THEN 'Store Contact' 

 -- Check for individual consumer 

 WHEN EXISTS(SELECT * FROM Sales.Customer AS c

 WHERE c.PersonID = @BusinessEntityID)

 THEN 'Consumer' 

 END;

6 在Having中用到CASE

 USE AdventureWorks2008R2;

 GO 

 SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate

 FROM HumanResources.Employee AS e

 JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID

 GROUP BY JobTitle

 HAVING (MAX(CASE WHEN Gender = 'M' 

 THEN ph1.Rate

 ELSE NULL END) > 40.00

 OR MAX(CASE WHEN Gender = 'F' 

 THEN ph1.Rate

 ELSE NULL END) > 42.00)

 ORDER BY MaximumRate DESC;

7 在SUM中用到CASE

 SELECT 

 SUM(CASE WHEN LEFT(promotion,1)='1' THEN 1 ELSE 0 END),

 SUM(CASE WHEN RIGHT(LEFT(promotion,3),1)='1' THEN 1 ELSE 0 END),

 SUM(CASE WHEN RIGHT(LEFT(promotion,17),1)='1' THEN 1 ELSE 0 END) FROM TableName