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 进行计算。
  • 返回 input_expression = when_expression 的第一个计算结果为 TRUE 的 result_expression。
  • 如果 input_expression = when_expression 的计算结果均不为 TRUE，则在指定了 ELSE 子句的情况下，SQL Server 数据库引擎将返回 else_result_expression；若没有指定 ELSE 子句，则返回 NULL 值。

CASE 搜索表达式：

  • 按指定顺序对每个 WHEN 子句的 Boolean_expression 进行计算。
  • 返回 Boolean_expression 的第一个计算结果为 TRUE 的 result_expression。
  • 如果 Boolean_expression 计算结果不为 TRUE，则在指定 ELSE 子句的情况下 数据库引擎将返回 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 功能的示例。