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

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel 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_expression input_expression
使用简单 CASE 格式时计算的表达式。Is the expression evaluated when the simple CASE format is used. input_expression 是任何有效的表达式input_expression is any valid expression.

WHEN when_expression WHEN 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_expression THEN 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_expression ELSE 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_expression WHEN Boolean_expression
使用 CASE 搜索格式时所计算的布尔表达式。Is the Boolean expression evaluated when using the searched CASE format. Boolean_expression 是任何有效的布尔表达式 。Boolean_expression is any valid Boolean expression.

备注

若要查看 SQL Server 2014 及更早版本的 Transact-SQL 语法,请参阅早期版本文档To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

返回类型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.

  • 返回首个 input_expression = when_expression 的计算结果为 TRUE 的 result_expression 。Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.

  • 如果 input_expression = when_expression 的计算结果均不为 TRUE,则在指定了 ELSE 子句的情况下,SQL Server 数据库引擎SQL Server Database Engine 将返回 else_result_expression;若没有指定 ELSE 子句,则返回 NULL 值 。If no input_expression = when_expression evaluates to TRUE, the SQL Server 数据库引擎SQL 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_expression 进行计算 。Evaluates, in the order specified, Boolean_expression for each WHEN clause.

  • 返回首个 Boolean_expression 的计算结果为 TRUE 的 result_expression 。Returns result_expression of the first Boolean_expression that evaluates to TRUE.

  • 如果 Boolean_expression 的计算结果均不为 TRUE,则在指定了 ELSE 子句的情况下,数据库引擎Database Engine 将返回 else_result_expression;若没有指定 ELSE 子句,则返回 NULL 值 。If no Boolean_expression evaluates to TRUE, the 数据库引擎Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

备注Remarks

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. 使用带有 CASE 简单表达式的 SELECT 语句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. 使用带有 CASE 搜索表达式的 SELECT 语句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 列等于“United States”时,结果集会按 CountryRegionName 列排序,对于所有其他行则按 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 表达式将根据该 BusinessEntityID 列是存在于 EmployeeVendor 还是存在于 Customer 表中来确定要为 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 Synapse Analytics(SQL 数据仓库)Azure Synapse Analytics (SQL Data Warehouse)并行数据仓库Parallel Data WarehouseExamples: Azure Synapse Analytics(SQL 数据仓库)Azure Synapse Analytics (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)