Выражение CASE (Transact-SQL)CASE (Transact-SQL)

  • Время чтения: 7 мин

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL AzureдаХранилище данных SQL AzureдаParallel Data WarehouseAPPLIES 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. Например, выражение CASE можно использовать в таких инструкциях, как SELECT, UPDATE, DELETE и SET, а также в таких предложениях, как select_list, IN, WHERE, ORDER BY и HAVING.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-SQLTopic 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
Простое выражение, с которым сравнивается input_expression при использовании простого формата CASE.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

Простое выражение CASESimple 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.

  • В указанном порядке сравнивает значения выражений input_expression и when_expression для каждого предложения WHEN.In the order specified, evaluates input_expression = when_expression for each WHEN clause.

  • Возвращает выражение result_expression, соответствующее первой операции input_expression = when_expression, равной TRUE.Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.

  • Если ни одна из операций input_expression = when_expression не дает значения TRUE, Компонент SQL Server Database EngineSQL Server Database Engine возвращает выражение else_result_expression, если указано предложение ELSE, или значение NULL, если предложение ELSE не указано.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.

Поисковое выражение CASESearched CASE expression:

  • Вычисляет в указанном порядке выражения Boolean_expression для каждого предложения WHEN.Evaluates, in the order specified, Boolean_expression for each WHEN clause.

  • Возвращает выражение result_expression, соответствующее первому выражению Boolean_expression, которое имеет значение TRUE.Returns result_expression of the first Boolean_expression that evaluates to TRUE.

  • Если ни одно выражение Boolean_expression не равно TRUE, Компонент Database EngineDatabase Engine возвращает выражение else_result_expression, если указано предложение ELSE, или значение NULL, если предложение ELSE не указано.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. Агрегатные выражения в аргументах WHEN выражения CASE вначале оцениваются, после чего передаются выражению 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 с простым выражением CASEUsing 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. Использование инструкции SELECT с поисковым выражением CASEUsing 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. Использование выражения CASE в предложении ORDER BYUsing CASE in an ORDER BY clause

В следующем примере выражение CASE используется в предложении ORDER BY, чтобы определить порядок сортировки строк на основе значения заданного столбца таблицы.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. Использование выражения CASE в инструкции UPDATEUsing CASE in an UPDATE statement

В следующем примере выражение CASE используется в инструкции UPDATE, чтобы определить значение, установленное в столбце VacationHours для сотрудников, у которых столбец SalariedFlag имеет значение 0.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. Если при вычитании 10 часов из VacationHours получается отрицательное значение, 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. Использование выражения CASE в инструкции SETUsing CASE in a SET statement

В следующем примере выражение CASE используется в инструкции SET для функции dbo.GetContactInfo с табличным значением.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 и соответствующий тип контакта для этого пользователя. Выражение CASE в инструкции SET определяет отображаемое значение для столбца ContactType в зависимости от наличия столбца BusinessEntityID в таблицах Employee, Vendor или Customer.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. Использование выражения CASE в предложении HAVINGUsing CASE in a HAVING clause

В следующем примере выражение CASE используется в предложении HAVING, чтобы ограничить строки, возвращаемые инструкцией 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;

Примеры: Хранилище данных SQL AzureAzure SQL Data Warehouse и Параллельное хранилище данныхParallel Data WarehouseExamples: Хранилище данных SQL AzureAzure SQL Data Warehouse and Параллельное хранилище данныхParallel Data Warehouse

Ж.G. Использование инструкции SELECT с выражением CASEUsing 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. Использование выражения CASE в инструкции UPDATEUsing CASE in an UPDATE statement

В следующем примере выражение CASE используется в инструкции UPDATE, чтобы определить значение, установленное в столбце VacationHours для сотрудников, у которых столбец SalariedFlag имеет значение 0.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. Если при вычитании 10 часов из VacationHours получается отрицательное значение, 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)