Bagikan melalui

CASE (Transact-SQL)

  • Artikel

Berlaku untuk: Titik akhir analitik SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Platform System (PDW) SQL di Microsoft Fabric Warehouse di Microsoft Fabric

Mengevaluasi daftar kondisi dan mengembalikan salah satu dari beberapa kemungkinan ekspresi hasil.

Ekspresi CASE memiliki dua format:

  • Ekspresi sederhana CASE membandingkan ekspresi dengan sekumpulan ekspresi sederhana untuk menentukan hasilnya.

  • Ekspresi yang dicari CASE mengevaluasi sekumpulan ekspresi Boolean untuk menentukan hasilnya.

Kedua format mendukung argumen ELSE opsional.

CASE dapat digunakan dalam pernyataan atau klausa apa pun yang memungkinkan ekspresi yang valid. Misalnya, Anda dapat menggunakan CASE dalam pernyataan seperti SELECT, UPDATE, DELETE dan SET, dan dalam klausa seperti <select_list>, IN, WHERE, ORDER BY, dan HAVING.

Konvensi sintaks transact-SQL

Sintaks

Sintaks untuk SQL Server, Azure SQL Database, dan 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

Sintaks untuk Gudang Data Paralel.

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

Argumen

input_expression

Ekspresi dievaluasi saat format sederhana CASE digunakan. input_expression adalah ekspresi yang valid.

KAPAN when_expression

Ekspresi sederhana yang input_expression dibandingkan saat format sederhana CASE digunakan. when_expression adalah ekspresi yang valid. Jenis data input_expression dan setiap when_expression harus sama atau harus berupa konversi implisit.

KEMUDIAN result_expression

Ekspresi yang dikembalikan saat input_expression sama dengan when_expression dievaluasi ke TRUE, atau Boolean_expression dievaluasi ke TRUE. ekspresi hasil adalah ekspresi yang valid.

else_result_expression LAIN

Ekspresi dikembalikan jika tidak ada operasi perbandingan yang dievaluasi ke TRUE. Jika argumen ini dihilangkan dan tidak ada operasi perbandingan yang dievaluasi ke TRUE, CASE mengembalikan NULL. else_result_expression adalah ekspresi yang valid. Jenis data else_result_expression dan result_expression apa pun harus sama atau harus berupa konversi implisit.

KAPAN Boolean_expression

Ekspresi Boolean dievaluasi saat menggunakan format yang dicari CASE . Boolean_expression adalah ekspresi Boolean yang valid.

Jenis yang dikembalikan

Mengembalikan jenis prioritas tertinggi dari kumpulan jenis dalam result_expressions dan else_result_expression opsional. Untuk informasi selengkapnya, lihat Prioritas Tipe Data (Transact-SQL).

Mengembalikan nilai

Ekspresi CASE sederhana:

Ekspresi sederhana CASE beroperasi dengan membandingkan ekspresi pertama dengan ekspresi di setiap klausa WHEN untuk kesetaraan. Jika ekspresi ini setara, ekspresi dalam klausa THEN akan dikembalikan.

  • Hanya mengizinkan pemeriksaan kesetaraan.

  • Dalam urutan yang ditentukan, mengevaluasi input_expression = when_expression untuk setiap klausa WHEN.

  • Mengembalikan result_expression when_expression input_expression = pertama yang dievaluasi ke TRUE.

  • Jika tidak ada input_expression = when_expression mengevaluasi ke TRUE, Mesin Database SQL Server mengembalikan else_result_expression jika klausul ELSE ditentukan, atau nilai NULL jika tidak ada klausa ELSE yang ditentukan.

Ekspresi CASE yang dicari:

  • Mengevaluasi, dalam urutan yang ditentukan, Boolean_expression untuk setiap klausa WHEN.

  • Mengembalikan result_expression Boolean_expression pertama yang dievaluasi ke TRUE.

  • Jika tidak ada Boolean_expression yang dievaluasi ke TRUE, Mesin Database mengembalikan else_result_expression jika klausul ELSE ditentukan, atau nilai NULL jika tidak ada klausa ELSE yang ditentukan.

Keterangan

SQL Server hanya memungkinkan 10 tingkat bersarang dalam CASE ekspresi.

Ekspresi CASE tidak dapat digunakan untuk mengontrol alur eksekusi pernyataan Transact-SQL, blok pernyataan, fungsi yang ditentukan pengguna, dan prosedur tersimpan. Untuk daftar metode control-of-flow, lihat Control-of-Flow Language (Transact-SQL).

Ekspresi CASE mengevaluasi kondisinya secara berurutan dan berhenti dengan kondisi pertama yang kondisinya terpenuhi. Dalam beberapa situasi, ekspresi dievaluasi sebelum CASE ekspresi menerima hasil ekspresi sebagai inputnya. Kesalahan dalam mengevaluasi ekspresi ini dimungkinkan. Ekspresi agregat yang muncul dalam argumen WHEN ke CASE ekspresi dievaluasi terlebih dahulu, lalu diberikan ke CASE ekspresi. Misalnya, kueri berikut menghasilkan kesalahan dibagi dengan nol saat menghasilkan nilai agregat MAX. Ini terjadi sebelum mengevaluasi CASE ekspresi.

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

Anda hanya boleh bergantung pada urutan evaluasi kondisi WHEN untuk ekspresi skalar (termasuk subkueri yang tidak berkorelasi yang mengembalikan skalar), bukan untuk ekspresi agregat.

Anda juga harus memastikan bahwa setidaknya salah satu ekspresi dalam klausa THEN atau ELSE bukan konstanta NULL. Meskipun NULL dapat dikembalikan dari beberapa ekspresi hasil, tidak semua ini dapat secara eksplisit menjadi konstanta NULL. Jika semua ekspresi hasil menggunakan konstanta NULL, kesalahan 8133 dikembalikan.

Contoh

J. Menggunakan pernyataan SELECT dengan ekspresi CASE sederhana

Dalam pernyataan SELECT , ekspresi sederhana CASE hanya memungkinkan pemeriksaan kesetaraan; tidak ada perbandingan lain yang dibuat. Contoh berikut menggunakan CASE ekspresi untuk mengubah tampilan kategori baris produk agar lebih dapat dimengerti.

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. Menggunakan pernyataan SELECT dengan ekspresi CASE yang dicari

SELECT Dalam pernyataan, ekspresi yang dicari CASE memungkinkan nilai diganti dalam tataan hasil berdasarkan nilai perbandingan. Contoh berikut menampilkan harga daftar sebagai komentar teks berdasarkan rentang harga untuk produk.

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. Menggunakan CASE dalam klausa ORDER BY

Contoh berikut menggunakan CASE ekspresi dalam klausul ORDER BY untuk menentukan urutan pengurutan baris berdasarkan nilai kolom tertentu. Dalam contoh pertama, nilai dalam SalariedFlag kolom HumanResources.Employee tabel dievaluasi. Karyawan yang memiliki SalariedFlag set ke 1 dikembalikan dalam BusinessEntityID urutan menurut. Karyawan yang memiliki SalariedFlag set ke 0 dikembalikan dalam urutan BusinessEntityID naik. Dalam contoh kedua, tataan hasil diurutkan menurut kolom TerritoryName saat kolom CountryRegionName sama dengan 'Amerika Serikat' dan untuk CountryRegionName semua baris lainnya.

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. Menggunakan CASE dalam pernyataan UPDATE

Contoh berikut menggunakan CASE ekspresi dalam pernyataan UPDATE untuk menentukan nilai yang diatur untuk kolom VacationHours untuk karyawan dengan SalariedFlag diatur ke 0. Ketika mengurangi 10 jam dari VacationHours menghasilkan nilai negatif, VacationHours ditingkatkan 40 jam; jika tidak, VacationHours ditingkatkan 20 jam. Klausa OUTPUT digunakan untuk menampilkan nilai sebelum dan sesudah liburan.

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

E. Menggunakan CASE dalam pernyataan SET

Contoh berikut menggunakan CASE ekspresi dalam pernyataan SET dalam fungsi dbo.GetContactInfobernilai tabel . AdventureWorks2022 Dalam database, semua data yang terkait dengan orang disimpan dalam Person.Person tabel. Misalnya, orang tersebut mungkin karyawan, perwakilan vendor, atau pelanggan. Fungsi mengembalikan nama depan dan belakang dari jenis kontak tertentu BusinessEntityID dan untuk orang tersebut. Ekspresi CASE dalam pernyataan SET menentukan nilai yang akan ditampilkan untuk kolom ContactType berdasarkan keberadaan BusinessEntityID kolom dalam Employeetabel , , Vendoratau 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. Menggunakan CASE dalam klausa HAVING

Contoh berikut menggunakan ekspresi dalam klausa CASE HAVING untuk membatasi baris yang dikembalikan oleh pernyataan SELECT. Pernyataan mengembalikan tarif per jam untuk setiap jabatan HumanResources.Employee pekerjaan dalam tabel. Klausul HAVING membatasi judul untuk mereka yang dipegang oleh karyawan bergaji dengan tingkat pembayaran maksimum lebih besar dari 40 dolar, atau karyawan non-salaried dengan tingkat pembayaran maksimum lebih besar dari 15 dolar.

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

Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)

G. Menggunakan pernyataan SELECT dengan ekspresi CASE

Dalam pernyataan SELECT, CASE ekspresi memungkinkan nilai diganti dalam tataan hasil berdasarkan nilai perbandingan. Contoh berikut menggunakan CASE ekspresi untuk mengubah tampilan kategori baris produk agar lebih dapat dimengerti. Ketika nilai tidak ada, teks "Tidak dijual' ditampilkan.

-- 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. Menggunakan CASE dalam pernyataan UPDATE

Contoh berikut menggunakan CASE ekspresi dalam pernyataan UPDATE untuk menentukan nilai yang diatur untuk kolom VacationHours untuk karyawan dengan SalariedFlag diatur ke 0. Ketika mengurangi 10 jam dari VacationHours menghasilkan nilai negatif, VacationHours ditingkatkan 40 jam; jika tidak, VacationHours ditingkatkan 20 jam.

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

Lihat juga