CASE (Transact-SQL)
Ocenia listę warunków i zwraca jedną z wielu możliwych wynik wyrażenia.
SPRAWY wyrażenie składa się z dwóch formatów:
Proste wyrażenie CASE porównuje wyrażenie zestaw wyrażeń prostych do obliczenia wyniku.
Wyszukiwanych przypadku wyrażenie przyjmuje, zestaw wyrażenia wartość logiczna do obliczenia wyniku.
Oba formaty obsługuje opcjonalny argument ELSE.
SPRAWY mogą być używane w żadnych instrukcja lub klauzula , która umożliwia prawidłowe wyrażenie.Na przykład można przypadku sprawozdania, takie jak SELECT, UPDATE, DELETE i zestawu i klauzule, takie jak select_list, w przypadku gdy ORDER BY i HAVING.
.gif "Ikona łącza do tematu")
Składnia
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
Argumenty
input_expression
wyrażenie sprawdzane jest używany format prosty sprawy.input_expressionwszelkie obowiązuje wyrażenie.KIEDYwhen_expression
Proste wyrażenie do którego jest input_expression porównywana jest używany format prosty przypadek.when_expressionto dowolne prawidłowe wyrażenie.Typy danych input_expression i każdego when_expression musi być taka sama lub musi być niejawna konwersja.NASTĘPNIEresult_expression
wyrażenie zwracany jest po input_expression jest równe when_expression wynikiem jest wartość TRUE, lub Boolean_expression ma wartość TRUE.result expressionwszelkie obowiązuje wyrażenie.ELSEelse_result_expression
Jest wyrażenie zwracane, jeśli operacja porównania nie ma wartość TRUE.Jeśli ten argument zostanie pominięty i operacja porównania nie ma wartość TRUE, przypadku zwraca wartość NULL.else_result_expressionto dowolne prawidłowe wyrażenie.Typy danych else_result_expression oraz wszelkie result_expression musi być taka sama lub musi być niejawna konwersja.KIEDYBoolean_expression
wyrażenie wartość logicznajest oceniany, korzystając z formatu liter wyszukiwanych. Boolean_expressionjest dowolny prawidłowy wartość logiczna wyrażenie.
Zwracane typy
Zwraca wartość typu najwyższy priorytet z zestaw typów w result_expressions oraz opcjonalny else_result_expression.Aby uzyskać więcej informacji, zobacz Pierwszeństwo typu danych (Transact-SQL).
Zwracanie wartości
Proste sprawy wyrażenie:
Proste wyrażenie CASE działa poprzez porównanie pierwszego wyrażenie wyrażenie w każdej po klauzula równoważności.Jeśli te wyrażenia są równoważne, wyrażenie w następnie klauzula zostaną zwrócone.
Umożliwia tylko wyboru równości.
Ocenia input_expression, a następnie w określonej kolejności input_expression = when_expression dla każdej gdy klauzula.
Zwraca result_expression pierwszego input_expression = when_expression , wynikiem jest TRUE.
Jeśli nie input_expression = when_expression wynikiem jest wartość TRUE, Aparat baz danych programu SQL Server zwraca else_result_expression Jeśli określono ELSE klauzula lub wartość NULL, jeśli nie określono żadnych ELSE, klauzula .
Wyszukiwane wyrażeniesprawy:
Ocenia, w kolejności określonej, Boolean_expression dla każdej gdy klauzula.
Zwraca result_expression pierwszego Boolean_expression , wynikiem jest TRUE.
Jeśli nie Boolean_expression wynikiem jest wartość TRUE, Aparat baz danych zwraca else_result_expression Jeśli określono ELSE klauzula lub wartość NULL, jeśli nie określono żadnych ELSE, klauzula .
Uwagi
SQL ServerUmożliwia tylko 10 poziomów zagnieżdżenia w przypadku wyrażeń.
SPRAWY wyrażenie nie można użyć do sterowania przepływem wykonania instrukcji języka Transact -SQL , bloki instrukcja , funkcje zdefiniowane przez użytkownika i procedur przechowywanych.Aby uzyskać listę metod kontroli przepływu, zobacz Sterowanie przepływem języka (Transact-SQL).
Przykłady
A.Za pomocą instrukcja SELECT CASE proste wyrażenie
W ramach SELECT instrukcja, prosty CASE wyrażenie umożliwia tylko sprawdzanie równości; nie są wprowadzane nie porównań.W poniższym przykładzie użyto CASE wyrażenie , aby zmienić sposób wyświetlania kategorii linii produktu, aby uczynić je bardziej zrozumiałym.
USE AdventureWorks2008R2;
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.Za pomocą instrukcja SELECT CASE wyszukiwanych wyrażenie
W ramach SELECTinstrukcja, wyszukiwanych CASEumożliwiawyrażenie wartości, które mają zostać zastąpione w zestaw wyników na podstawie porównania wartości. Poniższy przykład przedstawia cena katalogowa jako komentarz tekst na podstawie ceny zakres produktu.
USE AdventureWorks2008R2;
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.Aby zastąpić IIf funkcja , która jest używana w programie Microsoft Access przy użyciu sprawy
PRZYPADEK zawiera funkcje, które jest podobne do funkcja IIf w Microsoft dostępu.W poniższym przykładzie pokazano prostą kwerendę, która korzysta z IIf zsumować dane wyjściowe dla TelephoneInstructions kolumna programu Access tabela o nazwie db1.ContactInfo.
SELECT FirstName, LastName, TelephoneNumber,
IIf(IsNull(TelephoneInstructions),"Any time",
TelephoneInstructions) AS [When to Contact]
FROM db1.ContactInfo;
W poniższym przykładzie użyto CASE zsumować dane wyjściowe dla TelephoneSpecialInstructions kolumna w AdventureWorks2008R2 widoku Person.vAdditionalContactInfo.
USE AdventureWorks2008R2;
GO
SELECT FirstName, LastName, TelephoneNumber, 'When to Contact' =
CASE
WHEN TelephoneSpecialInstructions IS NULL THEN 'Any time'
ELSE TelephoneSpecialInstructions
END
FROM Person.vAdditionalContactInfo;
D.Za pomocą sprawy w PORZĄDKU według klauzula
Poniższe przykłady używa sprawy wyrażenie ORDER BY klauzula do ustalenia kolejność sortowania wierszy na podstawie wartości danej kolumna .W pierwszym przykładzie wartość w SalariedFlag kolumna HumanResources.Employee tabela jest oceniany.Pracownicy, których SalariedFlag zestaw na wartość 1 są zwracane w kolejności według EmployeeID w kolejności malejącej.Pracownicy, których SalariedFlag zestaw na wartość 0, zwracane są w kolejności według EmployeeID w kolejności rosnącej.W drugim przykładzie zestaw wyników jest uporządkowane według kolumna TerritoryName , gdy kolumna CountryRegionName jest równa "Stany Zjednoczone" oraz CountryRegionName dla wszystkich innych wierszy.
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;
E.W aktualizacji instrukcjaprzy użyciu sprawy
Poniższy przykład używa AKTUALIZUJĄCĄ instrukcja CASE wyrażenie do ustalenia wartość zestaw dla kolumnaVacationHours dla pracowników z SalariedFlagzestaw na wartość 0. Przy odejmowaniu godzin 10 z VacationHours wyniki ujemne wartości VacationHours jest zwiększana o 40 godzin; w przeciwnym razie VacationHours zostaje zwiększona przez 20 godzin.Dane wyjściowe klauzula jest używany do wyświetlania przed i po wartości urlopu.
USE AdventureWorks2008R2;
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;
F.Za pomocą sprawy w zestaw instrukcja
W poniższym przykładzie użyto sprawy wyrażenie w instrukcja SET w tabela-wycenione funkcja dbo.GetContactInfo.W AdventureWorks2008R2 bazy danych, wszystkie dane odnoszące się do osób są przechowywane w Person.Person tabela.Na przykład osoba może być pracownikiem, przedstawiciela dostawcy lub nabywcy.funkcja zwraca nazwę pierwszego i ostatniego danego BusinessEntityID i typ kontaktu dla tej osoby.SPRAWY wyrażenie w instrukcja SET określa wartość wyświetlaną dla kolumna ContactType oparte na istnienie BusinessEntityIDkolumna w Employee, Vendor, lub Customer tabele.
USE AdventureWorks2008R2;
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);
G.Za pomocą sprawy w klauzulaHAVING
W poniższym przykładzie użyto sprawy wyrażenie w klauzula HAVING, aby ograniczyć wierszy zwracanych przez instrukcjaSELECT.instrukcja zwraca maksymalną stawkę godzinową dla zadanie HumanResources.Employeetabela. klauzula HAVING ogranicza tytuły do tych, które są w posiadaniu mężczyzn z stawka płacy maksymalną większy niż 40 zł lub kobiet z stawka płacy maksymalną większą niż 42 dolarów.
USE AdventureWorks2008R2;
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;
Zobacz także