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

Мақала
10/16/2024

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure конечной точке аналитики платформы Аналитики Azure Synapse Analytics (PDW) в Microsoft Fabric Хранилище в Microsoft Fabric

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

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

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

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

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

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

Синтаксис

Синтаксис для SQL Server, База данных SQL Azure и 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

Синтаксис для параллельного хранилища данных.

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

Аргументы

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

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

Возвращает тип с наивысшим приоритетом из набора типов в выражении 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 ядро СУБД возвращает else_result_expression, если указано предложение ELSE, или значение NULL, если предложение ELSE не указано.

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

Вычисляет в указанном порядке выражения Boolean_expression для каждого предложения WHEN.
Возвращает выражение result_expression, соответствующее первому выражению Boolean_expression, которое имеет значение TRUE.
Если значение Boolean_expression равно TRUE, ядро СУБД возвращает else_result_expression, если указано предложение ELSE, или значение NULL, если не указано предложение ELSE.

Замечания

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

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

Выражение CASE оценивает условия последовательно и останавливается с первым условием, условие которого удовлетворено. В некоторых ситуациях выражение вычисляется до того, как CASE выражение получает результаты выражения в качестве входных данных. При оценке этих выражений возможны ошибки. Агрегированные выражения, которые отображаются в аргументах WHEN для CASE выражения, вычисляются сначала, а затем предоставляются выражению 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.

Примеры

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

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

USE AdventureWorks2022;
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 с поисковым выражением CASE

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

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

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

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

USE AdventureWorks2022;
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с табличным значением. В базе данных AdventureWorks2022 все данные, связанные с людьми, хранятся в таблице Person.Person. Например, человек может быть сотрудником, представителем поставщика или заказчиком. Функция возвращает имя и фамилию заданного BusinessEntityID и типа контакта для этого человека. Выражение CASE в инструкции SET определяет значение, отображаемое для столбца на основе существования столбца ContactType BusinessEntityID в EmployeeVendorтаблицах или Customer таблицах.

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

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

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

USE AdventureWorks2022;
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)

G. Использование инструкции 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

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

Бөлісу құралы: