CASE (Transact-SQL)CASE (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

評估一份條件清單,並傳回多個可能的結果運算式之一。Evaluates a list of conditions and returns one of multiple possible result expressions.

CASE 運算式有兩種格式:The CASE expression has two formats:

  • 簡單的 CASE 運算式會比較運算式和一組簡單運算式來得出結果。The simple CASE expression compares an expression to a set of simple expressions to determine the result.

  • 搜尋 CASE 運算會評估一組布林運算式來得出結果。The searched CASE expression evaluates a set of Boolean expressions to determine the result.

兩種格式都支援選用的 ELSE 引數。Both formats support an optional ELSE argument.

CASE 可以用在允許有效運算式的任何陳述式或子句中。CASE can be used in any statement or clause that allows a valid expression. 例如,您可以在 SELECT、UPDATE、DELETE 和 SET 之類的陳述式,以及 select_list、IN、WHERE、ORDER BY 和 HAVING 之類的子句中使用 CASE。For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
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  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
CASE  
     WHEN when_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END  

引數Arguments

input_expressioninput_expression
這是使用簡單的 CASE 格式時,所評估的運算式。Is the expression evaluated when the simple CASE format is used. input_expression 是任何有效的運算式input_expression is any valid expression.

WHEN when_expressionWHEN when_expression
這是使用簡單的 CASE 格式時,要與 input_expression 進行比較的簡單運算式。Is a simple expression to which input_expression is compared when the simple CASE format is used. when_expression 是任何有效的運算式。when_expression is any valid expression. input_expression 和每個 when_expression 的資料類型都必須相同,或必須能夠進行隱含轉換。The data types of input_expression and each when_expression must be the same or must be an implicit conversion.

THEN result_expressionTHEN result_expression
這是當 input_expression 等於 when_expression 評估為 TRUE,或 Boolean_expression 評估為 TRUE 時所傳回的運算式。Is the expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE. result expression 是任何有效的運算式result expression is any valid expression.

ELSE else_result_expressionELSE else_result_expression
這是沒有比較運算的評估結果是 TRUE 時,所傳回的運算式。Is the expression returned if no comparison operation evaluates to TRUE. 如果省略這個引數,且沒有比較運算得出 TRUE,CASE 就會傳回 NULL。If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression 是任何有效的運算式。else_result_expression is any valid expression. else_result_expression 和任何 result_expression 的資料類型都必須相同,或必須能夠進行隱含轉換。The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.

WHEN Boolean_expressionWHEN Boolean_expression
這是使用搜尋的 CASE 格式時,所評估的布林運算式。Is the Boolean expression evaluated when using the searched CASE format. Boolean_expression 是任何有效的布林值運算式。Boolean_expression is any valid Boolean expression.

傳回類型Return Types

result_expressions 和選擇性 else_result_expression 的類型集傳回優先順序最高的類型。Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression. 如需詳細資訊,請參閱資料類型優先順序 (Transact-SQL)For more information, see Data Type Precedence (Transact-SQL).

傳回值Return Values

簡單的 CASE 運算式:Simple CASE expression:

簡單的 CASE 運算式會透過比較第一個運算式與每個 WHEN 子句中的運算式是否相等來運算。The simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. 如果這些運算式相等,將會傳回 THEN 子句中的運算式。If these expressions are equivalent, the expression in the THEN clause will be returned.

  • 僅允許相等檢查。Allows only an equality check.

  • 依指定的順序來評估每個 WHEN 子句的 input_expression = when_expression。In the order specified, evaluates input_expression = when_expression for each WHEN clause.

  • 傳回第一個評估為 TRUE 之 input_expression = when_expressionresult_expressionReturns the result_expression of the first input_expression = when_expression that evaluates to TRUE.

  • 如果沒有任何 input_expression = when_expression 評估為 TRUE,若指定了 ELSE 子句,SQL Server Database EngineSQL Server Database Engine 就會傳回 else_result_expression,若未指定 ELSE 子句,則會傳回 NULL 值。If no input_expression = when_expression evaluates to TRUE, the SQL Server Database EngineSQL Server Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

搜尋的 CASE 運算式:Searched CASE expression:

  • 依指定的順序來評估每個 WHEN 子句的 Boolean_expressionEvaluates, in the order specified, Boolean_expression for each WHEN clause.

  • 傳回第一個評估為 TRUE 之 Boolean_expressionresult_expressionReturns result_expression of the first Boolean_expression that evaluates to TRUE.

  • 如果沒有任何 Boolean_expression 評估為 TRUE,若指定了 ELSE 子句,Database EngineDatabase Engine 就會傳回 else_result_expression,若未指定 ELSE 子句,則會傳回 NULL 值。If no Boolean_expression evaluates to TRUE, the Database EngineDatabase Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

RemarksRemarks

SQL ServerSQL Server 在 CASE 運算式中只允許 10 層的巢狀層級。allows for only 10 levels of nesting in CASE expressions.

CASE 運算式無法用於控制 Transact-SQL 陳述式、陳述式區塊、使用者定義函數以及預存程序的執行流程。The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures. 如需流程控制方法的清單,請參閱流程控制語言 (Transact-SQL)For a list of control-of-flow methods, see Control-of-Flow Language (Transact-SQL).

CASE 運算式會依序評估其條件,並在滿足其條件的第一個條件時停止。The CASE expression evaluates its conditions sequentially and stops with the first condition whose condition is satisfied. 在某些情況下,運算式會在 CASE 運算式收到運算式的結果作為其輸入之前進行評估。In some situations, an expression is evaluated before a CASE expression receives the results of the expression as its input. 評估這些運算式是否可能時發生錯誤。Errors in evaluating these expressions are possible. 針對 CASE 運算式,會先評估出現在 WHEN 引數中的彙總運算式,再將其提供給 CASE 運算式。Aggregate expressions that appear in WHEN arguments to a CASE expression are evaluated first, then provided to the CASE expression. 例如,下列查詢會在產生 MAX 彙總的值時,產生除以零的錯誤。For example, the following query produces a divide by zero error when producing the value of the MAX aggregate. 這個情況會在評估 CASE 運算式之前發生。This occurs prior to evaluating the CASE expression.

WITH Data (value) AS   
(   
SELECT 0   
UNION ALL   
SELECT 1   
)   
SELECT   
   CASE   
      WHEN MIN(value) <= 0 THEN 0   
      WHEN MAX(1/value) >= 100 THEN 1   
   END   
FROM Data ;  

針對純量運算式 (包括傳回純量的非相互關聯子查詢,而非針對彙總運算式),您應該僅相依於 WHEN 條件的評估順序。You should only depend on order of evaluation of the WHEN conditions for scalar expressions (including non-correlated sub-queries that return scalars), not for aggregate expressions.

範例Examples

A.A. 使用 SELECT 陳述式搭配簡單的 CASE 運算式Using a SELECT statement with a simple CASE expression

SELECT 陳述式內,只允許相等檢查使用簡單的 CASE 運算式,不能進行任何其他比較。Within a SELECT statement, a simple CASE expression allows for only an equality check; no other comparisons are made. 下列範例利用 CASE 運算式來變更產品線類別目錄的顯示方式,使它們更容易了解。The following example uses the CASE expression to change the display of product line categories to make them more understandable.

USE AdventureWorks2012;  
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.B. 使用 SELECT 陳述式搭配搜尋的 CASE 運算式Using a SELECT statement with a searched CASE expression

SELECT 陳述式內,搜尋的 CASE 運算式允許以比較值為基礎來取代結果集中的值。Within a SELECT statement, the searched CASE expression allows for values to be replaced in the result set based on comparison values. 下列範例以產品的價格範圍為基礎,將標價顯示為文字註解。The following example displays the list price as a text comment based on the price range for a product.

USE AdventureWorks2012;  
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.C. 在 ORDER BY 子句中使用 CASEUsing CASE in an ORDER BY clause

下列範例在 ORDER BY 子句中使用 CASE 運算式,根據給定的資料行值,決定資料列的排序次序。The following examples uses the CASE expression in an ORDER BY clause to determine the sort order of the rows based on a given column value. 在第一則範例中,系統會評估 SalariedFlag 資料表之 HumanResources.Employee 資料行的值。In the first example, the value in the SalariedFlag column of the HumanResources.Employee table is evaluated. SalariedFlag 設定為 1 的員工會以 BusinessEntityID 的遞減順序傳回。Employees that have the SalariedFlag set to 1 are returned in order by the BusinessEntityID in descending order. SalariedFlag 設定為 0 的員工會以 BusinessEntityID 的遞增順序傳回。Employees that have the SalariedFlag set to 0 are returned in order by the BusinessEntityID in ascending order. 在第二則範例中,結果集會依照資料行 TerritoryName 排序 (當資料行 CountryRegionName 等於 'United States' 時) 以及依照 CountryRegionName 排序 (針對所有其他資料列)。In the second example, the result set is ordered by the column TerritoryName when the column CountryRegionName is equal to 'United States' and by CountryRegionName for all other rows.

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;  
  

D.D. 在 UPDATE 陳述式中使用 CASEUsing CASE in an UPDATE statement

下列範例在 UPDATE 陳述式中使用 CASE 運算式來決定 VacationHours 設定為 0 時,針對員工之 SalariedFlag 資料行設定的值。The following example uses the CASE expression in an UPDATE statement to determine the value that is set for the column VacationHours for employees with SalariedFlag set to 0. VacationHours 減去 10 小時變成負值時,VacationHours 會加上 40 小時,否則 VacationHours 會加上 20 小時。When subtracting 10 hours from VacationHours results in a negative value, VacationHours is increased by 40 hours; otherwise, VacationHours is increased by 20 hours. OUTPUT 子句用於顯示假期值之前和之後。The OUTPUT clause is used to display the before and after vacation values.

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

E.E. 在 SET 陳述式中使用 CASEUsing CASE in a SET statement

下列範例會在資料表值函式 dbo.GetContactInfo 的 SET 陳述式中使用 CASE 運算式。The following example uses the CASE expression in a SET statement in the table-valued function dbo.GetContactInfo. AdventureWorks2012AdventureWorks2012 資料庫中,與人員相關的所有資料都會儲存在 Person.Person 資料表中。In the AdventureWorks2012AdventureWorks2012 database, all data related to people is stored in the Person.Person table. 例如,該人員可能是員工、廠商代表或客戶。For example, the person may be an employee, vendor representative, or a customer. 此函數會傳回給定之 BusinessEntityID 的姓名,以及該人員的連絡類型。SET 陳述式中的 CASE 運算式會根據 EmployeeVendorCustomer 資料表中的 BusinessEntityID 資料行是否存在,來決定要針對 ContactType 資料行顯示的值。The function returns the first and last name of a given BusinessEntityID and the contact type for that person.The CASE expression in the SET statement determines the value to display for the column ContactType based on the existence of the BusinessEntityID column in the Employee, Vendor, or Customer tables.

  
USE AdventureWorks2012;  
GO  
CREATE FUNCTION dbo.GetContactInformation(@BusinessEntityID int)  
    RETURNS @retContactInformation TABLE   
(  
BusinessEntityID int NOT NULL,  
FirstName nvarchar(50) NULL,  
LastName nvarchar(50) NULL,  
ContactType nvarchar(50) NULL,  
    PRIMARY KEY CLUSTERED (BusinessEntityID 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   
        @BusinessEntityID = BusinessEntityID,   
@FirstName = FirstName,   
        @LastName = LastName  
    FROM Person.Person   
    WHERE BusinessEntityID = @BusinessEntityID;  
  
    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;  
  
    -- Return the information to the caller  
    IF @BusinessEntityID IS NOT NULL   
    BEGIN  
        INSERT @retContactInformation  
        SELECT @BusinessEntityID, @FirstName, @LastName, @ContactType;  
    END;  
  
    RETURN;  
END;  
GO  
  
SELECT BusinessEntityID, FirstName, LastName, ContactType  
FROM dbo.GetContactInformation(2200);  
GO  
SELECT BusinessEntityID, FirstName, LastName, ContactType  
FROM dbo.GetContactInformation(5);  
  

F.F. 在 HAVING 子句中使用 CASEUsing CASE in a HAVING clause

下列範例會在 HAVING 子句中使用 CASE 運算式來限制 SELECT 陳述式所傳回的資料列。The following example uses the CASE expression in a HAVING clause to restrict the rows returned by the SELECT statement. 此陳述式會傳回 HumanResources.Employee 資料表中每個職稱的最高時薪。The statement returns the maximum hourly rate for each job title in the HumanResources.Employee table. HAVING 子句會將職稱限制為薪水上限大於 40 美金之男士所持有的職稱,以及薪水上限大於 42 美金之女士所持有的職稱。The HAVING clause restricts the titles to those that are held by men with a maximum pay rate greater than 40 dollars or women with a maximum pay rate greater than 42 dollars.

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

範例:Azure SQL 資料倉儲Azure SQL Data Warehouse平行處理資料倉儲Parallel Data WarehouseExamples: Azure SQL 資料倉儲Azure SQL Data Warehouse and 平行處理資料倉儲Parallel Data Warehouse

G.G. 使用 SELECT 陳述式搭配 CASE 運算式Using a SELECT statement with a CASE expression

在 SELECT 陳述式內,CASE 運算式允許以比較值為基礎來取代結果集中的值。Within a SELECT statement, the CASE expression allows for values to be replaced in the result set based on comparison values. 下列範例使用 CASE 運算式來變更產品線類別目錄的顯示方式,使它們更容易被了解。The following example uses the CASE expression to change the display of product line categories to make them more understandable. 若值並不存在,即會顯示 "Not for sale' 的字樣。When a value does not exist, the text "Not for sale' is displayed.

-- Uses AdventureWorks  
  
SELECT   ProductAlternateKey, 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,  
   EnglishProductName  
FROM dbo.DimProduct  
ORDER BY ProductKey;  

H.H. 在 UPDATE 陳述式中使用 CASEUsing CASE in an UPDATE statement

下列範例在 UPDATE 陳述式中使用 CASE 運算式來決定 VacationHours 設定為 0 時,針對員工之 SalariedFlag 資料行設定的值。The following example uses the CASE expression in an UPDATE statement to determine the value that is set for the column VacationHours for employees with SalariedFlag set to 0. VacationHours 減去 10 小時變成負值時,VacationHours 會加上 40 小時,否則 VacationHours 會加上 20 小時。When subtracting 10 hours from VacationHours results in a negative value, VacationHours is increased by 40 hours; otherwise, VacationHours is increased by 20 hours.

-- Uses AdventureWorks   
  
UPDATE dbo.DimEmployee  
SET VacationHours =   
    ( CASE  
         WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40  
         ELSE (VacationHours + 20.00)   
       END  
    )   
WHERE SalariedFlag = 0;  
  

另請參閱See Also

運算式 (Transact-SQL) Expressions (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
COALESCE (Transact-SQL) COALESCE (Transact-SQL)
IIF (Transact-SQL) IIF (Transact-SQL)
CHOOSE (Transact-SQL)CHOOSE (Transact-SQL)