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

Применимо к: SQL Server Azure SQL DatabaseУправляемый экземпляр SQL AzureAzure Synapse Analytics AnalyticsPlatform System (PDW)

Оценка списка условий и возвращение одного из нескольких возможных выражений результатов.

Выражение CASE имеет два формата:

• Простое CASE выражение сравнивает выражение с набором простых выражений, чтобы определить результат.

• Искомое CASE выражение вычисляет набор логических выражений для определения результата.

Оба формата поддерживают дополнительный аргумент ELSE.

CASE может использоваться в любом операторе или предложении, допускающих допустимое выражение. Например, можно использовать CASE в таких инструкциях, как SELECT, UPDATE, DELETE и SET, а также в таких предложениях, как <select_list>, IN, WHERE, ORDER BY и HAVING.

Соглашения о синтаксисе Transact-SQL

Синтаксис

Синтаксис для SQL Server, базы данных Azure SQL и Azure Synapse Analytics.

-- 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

Синтаксис для параллельных Data Warehouse.

CASE
     WHEN when_expression THEN result_expression [ ...n ]
     [ ELSE else_result_expression ]
END

Примечание

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

input_expression

Выражение, вычисляемое при использовании простого CASE формата. input_expression — это любое допустимое выражение.

WHEN when_expression

Простое выражение, с которым сравнивается input_expression при использовании простого CASE формата. 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 — это любое допустимое логическое выражение.

Примечание

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Типы возвращаемых данных

Возвращает тип с наивысшим приоритетом из набора типов в выражении result_expressions и необязательном выражении else_result_expression. Дополнительные сведения см. в разделе Приоритет типов данных (Transact-SQL).

Возвращаемые значения

Простое выражение CASE

Простое CASE выражение выполняется путем сравнения первого выражения с выражением в каждом предложении WHEN для эквивалентности. Если эти выражения эквивалентны, то возвращается выражение в предложении THEN.

• Допускается только проверка равенства.

• В указанном порядке сравнивает значения выражений input_expression и when_expression для каждого предложения WHEN.

• Возвращает выражение result_expression, соответствующее первой операции input_expression = when_expression, равной TRUE.

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

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

• Вычисляет в указанном порядке выражения Boolean_expression для каждого предложения WHEN.

• Возвращает выражение result_expression, соответствующее первому выражению Boolean_expression, которое имеет значение TRUE.

• Если ни одно выражение Boolean_expression не равно TRUE, Компонент Database Engine возвращает выражение else_result_expression, если указано предложение ELSE, или значение NULL, если предложение ELSE не указано.

Remarks

SQL Server допускает только 10 уровней вложенности в CASE выражениях.

Выражение CASE нельзя использовать для управления потоком выполнения инструкций Transact-SQL, блоков инструкций, определяемых пользователем функций и хранимых процедур. Список методов управления потоком см. в статье Язык управления потоком (Transact-SQL).

Выражение CASE оценивает свои условия последовательно и останавливается с первым условием, условие которого выполняется. В некоторых ситуациях выражение вычисляется до того, как CASE выражение получает результаты выражения в качестве входных данных. При оценке этих выражений возможны ошибки. Агрегированные выражения, отображаемые в параметре CASE WHEN, сначала вычисляются, а затем предоставляются выражению CASE . Например в следующем запросе создается ошибка деления на ноль при вычислении значения агрегата MAX. Это происходит перед вычислением CASE выражения.

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;
GO

Вы должны зависеть только от порядка вычисления условий WHEN для скалярных выражений (включая несоотносимые вложенные запросы, возвращающие скаляры), а не для агрегированных выражений.

Кроме того, необходимо убедиться, что по крайней мере одно из выражений в предложениях THEN или ELSE не является константой NULL. Хотя значение NULL может быть возвращено из нескольких результирующих выражений, не все из них могут явно быть константой NULL. Если во всех результирующих выражениях используется константа NULL, возвращается ошибка 8133.

Примеры

A. Использование инструкции SELECT с простым выражением CASE

При использовании в инструкции SELECT простое выражение CASE позволяет выполнить только проверку на равенство. Другие проверки не выполняются. В следующем примере выражение CASE используется для изменения способа отображения категорий линейки продуктов с целью сделать их более понятными.

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

Б. Использование инструкции SELECT с искомыми выражениями CASE

При использовании в инструкции SELECT поисковое выражение CASE позволяет заменять значения в результирующем наборе в зависимости от результатов сравнения. В следующем примере отображается список цен в виде текстового комментария, основанного на диапазоне цен для продукта.

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

В. Использование CASE в предложении ORDER BY

В следующих примерах выражение используется CASE в предложении ORDER BY для определения порядка сортировки строк на основе заданного значения столбца. В первом примере вычисляется значение столбца SalariedFlag таблицы HumanResources.Employee. Сотрудники, для которых столбец SalariedFlag имеет значение 1, возвращаются в порядке BusinessEntityID (по убыванию). Сотрудники, для которых столбец SalariedFlag имеет значение 0, возвращаются в порядке BusinessEntityID (по возрастанию). Во втором примере результирующий набор упорядочивается по столбцу TerritoryName, если столбец CountryRegionName содержит значение «США», и по столбцу CountryRegionName в остальных строках.

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;
GO

Г. Использование CASE в инструкции UPDATE

В следующем примере выражение в инструкции CASE UPDATE используется для определения значения, заданного для столбца VacationHours для сотрудников с SalariedFlag значением 0. Если при вычитании 10 часов из VacationHours получается отрицательное значение, VacationHours увеличивается на 40 часов. В противном случае значение VacationHours увеличивается на 20 часов. С помощью предложения OUTPUT отображаются исходная и обновленная продолжительности отпуска.

USE AdventureWorks2019;
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;
GO

Д. Использование CASE в инструкции SET

В следующем примере выражение используется CASE в инструкции SET в функции dbo.GetContactInfoс табличным значением . В базе данных AdventureWorks2019 все данные, связанные с людьми, хранятся в таблице Person.Person. Например, человек может быть сотрудником, представителем поставщика или заказчиком. Функция возвращает имя и фамилию заданного BusinessEntityID пользователя, а также тип контакта для этого человека. Выражение CASE в инструкции SET определяет значение, отображаемое для столбцаContactType, на основе наличия столбца в Employeeтаблицах BusinessEntityID , Vendorили Customer .

USE AdventureWorks2019;
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);
GO

Е. Использование CASE в предложении HAVING

В следующем примере выражение в предложении CASE HAVING используется для ограничения строк, возвращаемых инструкцией SELECT. Оператор возвращает почасовую ставку для каждой должности HumanResources.Employee в таблице. Предложение HAVING ограничивает названия теми, которые удерживаются наемными работниками с максимальной ставкой заработной платы более 40 долларов, или неоплачиваемых сотрудников с максимальной ставкой заработной платы более 15 долларов.

USE AdventureWorks2019;
GO

SELECT JobTitle,
    MAX(ph1.Rate) AS MaximumRate
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeePayHistory AS ph1
    ON e.BusinessEntityID = ph1.BusinessEntityID
GROUP BY JobTitle
HAVING (
        MAX(CASE
                WHEN SalariedFlag = 1 THEN ph1.Rate
                ELSE NULL
                END) > 40.00
        OR MAX(CASE
                WHEN SalariedFlag = 0 THEN ph1.Rate
                ELSE NULL
                END) > 15.00
        )
ORDER BY MaximumRate DESC;
GO

Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)

Ж. Использование инструкции SELECT с выражением CASE

В инструкции CASE SELECT выражение позволяет заменять значения в результирующем наборе на основе значений сравнения. В следующем примере выражение CASE используется для изменения способа отображения категорий линейки продуктов с целью сделать их более понятными. Если значение не существует, отображается текст "Не продается".

-- 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;
GO

H. Использование CASE в инструкции UPDATE

В следующем примере выражение в инструкции CASE UPDATE используется для определения значения, заданного для столбца VacationHours для сотрудников с SalariedFlag значением 0. Если при вычитании 10 часов из VacationHours получается отрицательное значение, VacationHours увеличивается на 40 часов. В противном случае значение VacationHours увеличивается на 20 часов.

-- Uses AdventureWorks

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

См. также раздел

• Выражения (Transact-SQL)
• SELECT (Transact-SQL)
• COALESCE (Transact-SQL)
• Предложение IIF (Transact-SQL)
• CHOOSE (Transact-SQL)