CASE (Transact-SQL)

  • 發行項

更新： 2006 年 4 月 14 日

評估一份條件清單，傳回多個可能的結果運算式之一。

CASE 運算式有兩種格式：

  • 簡單 CASE 運算式會比較運算式和一組簡單運算式來得出結果。
  • 搜尋 CASE 運算式會評估一組布林運算式來得出結果。

兩種格式都支援選用的 ELSE 引數。

CASE 可用於允許有效運算式的任何陳述式或子句中。例如，您可以在 SELECT、UPDATE、DELETE 和 SET 等陳述式以及 select_list、IN、WHERE、ORDER BY 和 HAVING 等子句中使用 CASE。

主題連結圖示Transact-SQL 語法慣例

語法

Simple CASE expression: 
CASE input_expression 
     WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END 
Searched CASE expression:
CASE
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

引數

  • input_expression
    這是使用簡單的 CASE 格式時所評估的運算式。input_expression 是任何有效的運算式。
  • WHEN when_expression
    這是使用簡單的 CASE 格式時，input_expression 所比較的簡單運算式。when_expression 是任何有效的運算式。input_expression 和每個 when_expression 的資料類型都必須相同，或必須能夠隱含地轉換。
  • THEN result_expression
    這是 input_expression 等於 when_expression 得出 TRUE 或 Boolean_expression 得出 TRUE 時，所傳回的運算式。result expression 是任何有效的運算式。
  • ELSE else_result_expression
    這是沒有比較運算的評估結果是 TRUE 時，所傳回的運算式。如果省略這個引數，且沒有比較運算得出 TRUE，CASE 便傳回 NULL。else_result_expression 是任何有效的運算式。else_result_expression 和每個 result_expression 的資料類型都必須相同，或必須能夠隱含地轉換。
  • WHEN Boolean_expression
    這是使用搜尋的 CASE 格式時，所評估的布林運算式。Boolean_expression 是任何有效的布林運算式。

結果類型

從 result_expressions 和選擇性的 else_result_expression 中的類型集中，傳回優先順序最高的類型。如需詳細資訊，請參閱＜資料類型優先順序 (Transact-SQL)＞。

備註

SQL Server 在 CASE 運算式中只允許 10 層的巢狀層級。

CASE 運算式無法用來控制 Transact-SQL 陳述式、陳述式區塊、使用者定義函數和預存程序的執行流程。如需流程控制方法的清單，請參閱＜流程控制語言 (Transact-SQL)＞。

結果值

簡單 CASE 運算式：

簡單 CASE 運算式的運作方式是比較第一個運算式與每個 WHEN 子句中的運算式是否相等。如果這些運算式相等，就會傳回 THEN 子句中的運算式。

  • 僅允許相等檢查。
  • 評估 input_expression，之後，再依照指定的順序來評估每個 WHEN 子句的 input_expression = when_expression。
  • 傳回得出 TRUE 的第一個 input_expression = when_expression 之 result_expression。
  • 如果沒有 input_expression = when_expression 得出 TRUE，當指定了 ELSE 子句時，SQL Server Database Engine 會傳回 else_result_expression，當未指定 ELSE 子句時，會傳回 NULL 值。

搜尋 CASE 運算式：

  • 依照指定順序來評估每個 WHEN 子句的 Boolean_expression。
  • 傳回得出 TRUE 的第一個 Boolean_expression 之 result_expression。
  • 如果沒有 Boolean_expression 得出 TRUE，當指定了 ELSE 子句時，Database Engine 會傳回 else_result_expression，當未指定 ELSE 子句時，會傳回 NULL 值。

範例

A. 搭配簡單 CASE 運算式使用 SELECT 陳述式

在 SELECT 陳述式內，簡單 CASE 運算式只允許相等檢查，不能進行任何其他比較。下列範例利用 CASE 運算式來變更產品線類別目錄的顯示方式，使它們更容易了解。

USE AdventureWorks;
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

B. 搭配搜尋 CASE 運算式使用 SELECT 陳述式

在 SELECT 陳述式內，搜尋 CASE 運算式允許以比較值為基礎來取代結果集中的值。下列範例以產品的價格範圍為基礎，將標價顯示為文字註解。

USE AdventureWorks;
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

C. 使用 CASE 來取代用於 Microsoft Access 的 IIf 函數

CASE 提供類似於 Microsoft Access 中 IIf 函數的功能。下列範例顯示使用 IIf 的簡單查詢，來為名稱為 db1.ContactInfo 之 Access 資料表中的 TelephoneInstructions 資料行提供輸出值。

SELECT FirstName, LastName, TelephoneNumber, 
     IIf(IsNull(TelephoneInstructions),"Any time",
     TelephoneInstructions) AS [When to Contact]
FROM db1.ContactInfo;

下列範例使用 CASE 來為 AdventureWorks 檢視表中的 TelephoneSpecialInstructions 資料行提供輸出值 Person.vAdditionalContactInfo。

USE AdventureWorks;
GO
SELECT FirstName, LastName, TelephoneNumber, 'When to Contact' = 
     CASE
          WHEN TelephoneSpecialInstructions IS NULL THEN 'Any time'
          ELSE TelephoneSpecialInstructions
     END
FROM Person.vAdditionalContactInfo;

D. 在 ORDER BY 子句中使用 CASE

下列範例會在 ORDER BY 子句中使用 CASE 運算式，根據 HumanResources.Employee 資料表之 SalariedFlag 資料行中的值判斷資料列的排序次序。系統會依照 EmployeeID 的順序以遞減順序傳回 SalariedFlag 設定為 1 的員工。系統會依照 EmployeeID 的順序以遞增順序傳回 SalariedFlag 設定為 0 的員工。

SELECT EmployeeID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN EmployeeID END DESC
        ,CASE WHEN SalariedFlag = 0 THEN EmployeeID END;
GO

E. 在 UPDATE 陳述式中使用 CASE

下列範例會在 UPDATE 陳述式中使用 CASE 運算式，針對 SalariedFlag 設定為 0 的員工判斷 VacationHours 資料行所設定的值。如果從 VacationHours 中減去 10 小時會產生負值，VacationHours 就會增加 40 小時，否則 VacationHours 就會增加 20 小時。OUTPUT 子句是用來顯示休假之前和之後的值。

USE AdventureWorks;
GO
UPDATE HumanResources.Employee
SET VacationHours = 
    ( CASE
         WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
         ELSE (VacationHours + 20.00)
       END
    )
OUTPUT Deleted.EmployeeID, Deleted.VacationHours AS BeforeValue, 
       Inserted.VacationHours AS AfterValue
WHERE SalariedFlag = 0;

F. 在 SET 陳述式中使用 CASE

下列範例會在資料表值函數 dbo.GetContactInfo 的 SET 陳述式中使用 CASE 運算式。在 AdventureWorks 資料庫中，與人員相關的所有資料都會儲存在 Person.Contact 資料表中。例如，人員可能是指員工、供應商代表、店家代表或消費者。此函數會傳回給定 ContactID 的名字和姓氏以及該位人員的連絡人類型。SET 陳述式中的 CASE 運算式會根據 ContactID 存在 Employee、StoreContact、VendorContact 或 Individual (消費者) 資料表中，判斷要針對 ContactType 資料行顯示的值。

USE AdventureWorks;
GO
CREATE FUNCTION dbo.GetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE 
(
    ContactID int NOT NULL,
    FirstName nvarchar(50) NULL,
    LastName nvarchar(50) NULL,
    ContactType nvarchar(50) NULL,
    PRIMARY KEY CLUSTERED (ContactID ASC)
) 
AS 
-- Returns the first name, last name and contact type for the specified contact.
BEGIN
    DECLARE 
        @FirstName nvarchar(50), 
        @LastName nvarchar(50), 
        @ContactType nvarchar(50);

    -- Get common contact information
    SELECT 
        @ContactID = ContactID, 
        @FirstName = FirstName, 
        @LastName = LastName
    FROM Person.Contact 
    WHERE ContactID = @ContactID;

    SET @ContactType = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e 
                WHERE e.ContactID = @ContactID) 
                THEN 'Employee'

            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Purchasing.VendorContact AS vc 
                    INNER JOIN Person.ContactType AS ct 
                    ON vc.ContactTypeID = ct.ContactTypeID 
                WHERE vc.ContactID = @ContactID) 
                THEN 'Vendor Contact'

            -- Check for store
            WHEN EXISTS(SELECT * FROM Sales.StoreContact AS sc 
                    INNER JOIN Person.ContactType AS ct 
                    ON sc.ContactTypeID = ct.ContactTypeID 
                WHERE sc.ContactID = @ContactID) 
                THEN 'Store Contact'

            -- Check for individual consumer
            WHEN EXISTS(SELECT * FROM Sales.Individual AS i 
                WHERE i.ContactID = @ContactID) 
                THEN 'Consumer'
        END;

    -- Return the information to the caller
    IF @ContactID IS NOT NULL 
    BEGIN
        INSERT @retContactInformation
        SELECT @ContactID, @FirstName, @LastName, @ContactType;
    END;

    RETURN;
END;
GO
SELECT ContactID, FirstName, LastName, ContactType
FROM dbo.GetContactInformation(2200);
GO
SELECT ContactID, FirstName, LastName, ContactType
FROM dbo.GetContactInformation(5);

G. 在 HAVING 子句中使用 CASE

下列範例會在 HAVING 子句中使用 CASE 運算式，以便限制 SELECT 陳述式所傳回的資料列。此陳述式會針對 HumanResources.Employee 資料表中的每個職稱傳回最大時薪。HAVING 子句會將這些職稱限制為男性最大時薪超過 40 美元或女性最大時薪超過 42 美元的職稱。

USE AdventureWorks;
GO
SELECT Title, MAX(ph1.Rate)AS MaximumRate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 ON e.EmployeeID = ph1.EmployeeID
GROUP BY Title
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;

請參閱

參考

運算式 (Transact-SQL)
SELECT (Transact-SQL)
COALESCE (Transact-SQL)

其他資源

使用 CASE

說明及資訊

取得 SQL Server 2005 協助

變更歷程記錄

版本 歷程記錄

2006 年 4 月 14 日
新增內容：
  • 新增使用 CASE 來示範 IIf 功能的範例。