A. SELECT ステートメントを単純 CASE 式と共に使用する
SELECT ステートメント内では、単純 CASE 式は等しいかどうかのチェックだけを実行できます。これ以外の比較操作は実行できません。CASE 式を使用して、製品ラインのカテゴリの表示をわかりやすいものに変更する例を次に示します。
USE AdventureWorks;
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 AdventureWorks;
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. Microsoft Access で使用される IIf 関数を CASE で置き換える
CASE は、Microsoft Access の IIf 関数と同様の機能を提供します。次の例は、IIf を使用して、db1.ContactInfo という名前の Access テーブルの TelephoneInstructions 列に値を出力する簡単なクエリを示しています。
SELECT FirstName, LastName, TelephoneNumber,
IIf(IsNull(TelephoneInstructions),"Any time",
TelephoneInstructions) AS [When to Contact]
FROM db1.ContactInfo; 次の例では、CASE を使用して、AdventureWorks の Person.vAdditionalContactInfo ビューの TelephoneSpecialInstructions 列に値を出力します。
USE AdventureWorks;
GO
SELECT FirstName, LastName, TelephoneNumber, 'When to Contact' =
CASE
WHEN TelephoneSpecialInstructions IS NULL THEN 'Any time'
ELSE TelephoneSpecialInstructions
END
FROM Person.vAdditionalContactInfo;
D. ORDER BY 句で CASE を使用する
次の例では、ORDER BY 句で CASE 式を使用して、指定された列の値に基づいて行の並べ替え順序を決定しています。最初の例では、HumanResources.Employee テーブルの SalariedFlag 列の値を評価します。SalariedFlag が 1 に設定されている従業員は EmployeeID の降順で、SalariedFlag が 0 に設定されている従業員は EmployeeID の昇順で返されます。2 番目の例では、列 CountryRegionName が 'United States' と等しい場合は結果セットが列 TerritoryName によって並べ替えられ、他のすべての列は CountryRegionName によって並べ替えられます。
SELECT EmployeeID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN EmployeeID END DESC
,CASE WHEN SalariedFlag = 0 THEN EmployeeID END;
GO
SELECT SalesPersonID, LastName, TerritoryName, CountryRegionName
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL
ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName
ELSE CountryRegionName END;
E. UPDATE ステートメントで CASE を使用する
次の例では、UPDATE ステートメントで CASE 式を使用して、SalariedFlag が 0 に設定されている従業員の VacationHours 列の値を決定しています。VacationHours の値を 10 時間差し引くと値がマイナスになる場合は 40 時間、それ以外の場合は 20 時間、VacationHours の値を増やします。OUTPUT 句は、この処理の前後の VacationHours の値を表示するために使用されています。
USE AdventureWorks;
GO
UPDATE HumanResources.Employee
SET VacationHours =
( CASE
WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
ELSE (VacationHours + 20.00)
END
)
OUTPUT Deleted.EmployeeID, Deleted.VacationHours AS BeforeValue,
Inserted.VacationHours AS AfterValue
WHERE SalariedFlag = 0;
F. SET ステートメントで CASE を使用する
次の例では、テーブル値関数 dbo.GetContactInfo の SET ステートメントで CASE 式を使用しています。AdventureWorks データベースでは、人に関連するデータはすべて Person.Contact テーブルに格納されています。たとえば、従業員、仕入先の代表者、小売り店の代表者、消費者などはすべて人に関連するデータとして扱われます。この関数は、指定した ContactID の氏名と連絡先タイプを返します。ContactType 列に表示される値は、SET ステートメント内の CASE 式により、Employee、StoreContact、VendorContact、または Individual (消費者) のどのテーブルに ContactID 列が含まれているかに基づいて決定されます。
USE AdventureWorks;
GO
CREATE FUNCTION dbo.GetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
ContactID int NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
ContactType nvarchar(50) NULL,
PRIMARY KEY CLUSTERED (ContactID 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
@ContactID = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact AS vc
INNER JOIN Person.ContactType AS ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact AS sc
INNER JOIN Person.ContactType AS ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual AS i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @ContactType;
END;
RETURN;
END;
GO
SELECT ContactID, FirstName, LastName, ContactType
FROM dbo.GetContactInformation(2200);
GO
SELECT ContactID, FirstName, LastName, ContactType
FROM dbo.GetContactInformation(5);
G. HAVING 句で CASE を使用する
次の例では、HAVING 句で CASE 式を使用して、SELECT ステートメントで返される行を制限しています。このステートメントは、HumanResources.Employee テーブル内の各役職の最も高い時給を返します。HAVING 句では、最も高い時給が男性の場合には 40 ドル、女性の場合には 42 ドルを超えている役職のみが返されるように制限しています。
USE AdventureWorks;
GO
SELECT Title, MAX(ph1.Rate)AS MaximumRate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 ON e.EmployeeID = ph1.EmployeeID
GROUP BY Title
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;