Itzik Ben-Gan
Solid Quality Learning
October 2003
日本語版最終更新日 2003 年 12 月 9 日
対象 :
Microsoft® SQL Server™ "Yukon" Beta 1
概要 : 本書では、Microsoft SQL Server "Yukon" Beta 1 で Transact-SQL に対して新しく強化された機能をいくつか紹介します。 これらの新しい機能により、表現力、クエリのパフォーマンス、エラー管理機能を向上できます。 本書では、概念的に新しい、リレーションの機能強化に主に重点を置いており、実例を使用して新しい機能を例示します。 本書には、新しい Transact-SQL 機能すべての説明が含まれているわけではありません。
目次
.gif)
紹介と対象範囲
クエリの表現力と DRI サポートの向上
パフォーマンスとエラー処理の機能強化
Transact-SQL に影響を与える SQL Server "Yukon" Beta 1 の他の機能
まとめ
紹介と対象範囲
本書では、Microsoft SQL Server "Yukon" Beta 1 で Transact-SQL に対して新しく強化された機能をいくつか紹介します。 これらの新しい機能により、表現力、クエリのパフォーマンス、エラー管理機能を向上できます。 本書では、概念的に新しい、リレーションの機能強化に主に重点を置いており、実例を使用して新しい機能を例示します。 本書には、新しい Transact-SQL 機能すべての説明が含まれているわけではありません。
前提となる知識 : 対象読者は、Microsoft SQL Server 2000 のアドホック クエリに、および Microsoft SQL Server 2000 のアプリケーションのコンポーネントとして、Transact-SQL を使用する技術を持っている必要があります。
クエリの表現力と DRI サポートの向上
ここでは、次の新しいリレーショナル機能と強化された機能を紹介します。
再帰クエリと共通テーブル式
本書では、再帰 CTE 式の優れた点を探求し、従来のアプローチを非常に簡素化する方法での一般的な問題点に対するソリューションとして再帰的な CTE 式を適用します。
共通テーブル式
共通テーブル式 (CTE) は、定義しているステートメントによって参照できる名前付きの一時的な結果セットです。 CTE の単純な形式では、CTE を派生テーブルの向上したバージョンと見なすことができます。このバージョンは、非永続的なビューと非常に似ています。 派生テーブルとビューを参照する方法と同様、クエリの FROM 句で CTE を参照します。 CTE は一度だけ定義します。その後、クエリ内で CTE を数回参照できます。 CTE の定義では、同一バッチ内に定義された変数を参照できます。 また、ビューを使用する方法と同様、INSERT、UPDATE、DELETE、および CREATE VIEW の各ステートメントにも CTE を使用できます。 ただし、CTE の実際の能力は、CTE が CTE 自体を参照する再帰的な機能にあります。 本書では、前半に単純な形式の CTE、後半に再帰的な形式の CTE を説明します。 また、本書では、CTE を使った SELECT クエリを網羅しています。
クエリ結果を参照するときに、クエリ結果がテーブルであるかのように派生テーブルを使用しますが、データベースに永続的なビューを作成することは望みません。 ただし、派生テーブルには、CTE による緩やかな制限があります。 この制限として、クエリ内で一度定義した派生テーブルを数回使用することはできません。 代わりに、同じクエリを含むいくつかの派生テーブルを定義する必要があります。 代替策として、CTE を一度定義して、データベース内で CTE を保存せずに、クエリ内でその CTE を数回使用することができます。
CTE の実例を提供する前に、CTE の基本構文を派生テーブルおよびビューと比較します。 以下は、ビュー、派生テーブル、および CTE 内のクエリの一般的な形式です。
ビュー
CREATE VIEW <view_name>(<column_aliases>)
AS
<view_query>
GO
SELECT *
FROM <view_name>
派生テーブル
SELECT *
FROM (<derived_table_query>) AS <derived_table_alias>(<column_aliases>)
CTE
WITH <cte_alias>(<column_aliases>)
AS
(
<cte_query>
)
SELECT *
FROM <cte_alias>
WITH キーワードの後に、CTE の別名および別名の一覧 (省略可能) を指定し、その本体を記述し、その本体を外部クエリから参照します。
CTE の WITH 句がバッチ内の最初のステートメントでない場合、先行するステートメントと WITH 句を含むステートメントをセミコロン (;) で区切る必要があることに注意してください。セミコロンは、WITH 句を含むステートメントの前に配置します。 セミコロンは、WITH 句の他の用途 (たとえば、テーブル ヒント) との曖昧さをなくすために使用します。 すべての場合にセミコロンを指定する必要があるわけではありませんが、セミコロンを一貫して使用することをお勧めします。
実例として、Northwind データベースの Employees および Orders テーブルを考えます。 各従業員は、ReportsTo 列で指定されたマネージャに直属します。 Employees テーブルの各従業員は、Orders テーブル内の注文に関連させることができます。 従業員ごとに注文数と最終注文日付を返し、同じ行にマネージャに対して同様の詳細を返すとします。 以下の例で、ビュー、派生テーブル、および CTE を使用してソリューションを実装できる方法を示します。
ビュー
CREATE VIEW VEmpOrders(EmployeeID, NumOrders, MaxDate)
AS
SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Orders
GROUP BY EmployeeID
GO
SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate,
E.ReportsTo, OM.NumOrders, OM.MaxDate
FROM Employees AS E
JOIN VEmpOrders AS OE
ON E.EmployeeID = OE.EmployeeID
LEFT OUTER JOIN VEmpOrders AS OM
ON E.ReportsTo = OM.EmployeeID
派生テーブル
SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate,
E.ReportsTo, OM.NumOrders, OM.MaxDate
FROM Employees AS E
JOIN (SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Orders
GROUP BY EmployeeID) AS OE(EmployeeID, NumOrders, MaxDate)
ON E.EmployeeID = OE.EmployeeID
LEFT OUTER JOIN
(SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Orders
GROUP BY EmployeeID) AS OM(EmployeeID, NumOrders, MaxDate)
ON E.ReportsTo = OM.EmployeeID
CTE
WITH EmpOrdersCTE(EmployeeID, NumOrders, MaxDate)
AS
(
SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Orders
GROUP BY EmployeeID
)
SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate,
E.ReportsTo, OM.NumOrders, OM.MaxDate
FROM Employees AS E
JOIN EmpOrdersCTE AS OE
ON E.EmployeeID = OE.EmployeeID
LEFT OUTER JOIN EmpOrdersCTE AS OM
ON E.ReportsTo = OM.EmployeeID
CTE は外部クエリの前に定義する必要があります。ただし、CTE の定義は参照する場合としない場合があります。 他の介入しているステートメントの後のバッチ内で CTE を後で参照することはできません。
1 つの WITH 句にいくつかの CTE を定義して、それぞれが前に定義した CTE を参照するように指定できます。 CTE を区切るには、コンマを使用します。 たとえば、従業員の注文数の最大値、最小値、および差異を計算するとします。
WITH
EmpOrdersCTE(EmployeeID, Cnt)
AS
(
SELECT EmployeeID, COUNT(*)
FROM Orders
GROUP BY EmployeeID
),
MinMaxCTE(MN, MX, Diff)
AS
(
SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)
FROM EmpOrdersCTE
)
SELECT * FROM MinMaxCTE
結果セットは以下のようになります。
MN MX Diff
----------- ----------- -----------
42 156 114
EmpOrdersCTE で、各従業員の注文数を計算し、MinMaxCTE で、EmpOrdersCTE を参照してその数の最大値、最小値、および差異を計算します。
注意 CTE 内では、CTE の直前に定義された CTE しか参照できないという制限はありません。前に定義されたすべての CTE を参照できます。 前方参照は許可されていないことに注意してください。 つまり、ある CTE はそれより前に定義された CTE とその CTE 自体 (本書後半の「再帰クエリ」を参照) を参照できますが、その CTE の後に定義された CTE を参照することはできません。 1 つの WITH ステートメントに CTE C1、C2、C3 を定義した場合、C2 は C1 と C2 を参照できますが、C3 を参照することはできません。
別の例として、以下のコードでは、最小値と最大値間の注文数の 4 つの範囲それぞれに含まれる従業員の数を計算するヒストグラムが生成されます。 計算が少し複雑に感じる場合、この計算を理解することに時間を費やす必要はありません。 この例の目的は、以下のような実用的なシナリオを使用して、1 つの WITH ステートメントでの複数の CTE の宣言を例示することにあります。
WITH
EmpOrdersCTE(EmployeeID, Cnt)
AS
(
SELECT EmployeeID, COUNT(*)
FROM Orders
GROUP BY EmployeeID
),
MinMaxCTE(MN, MX, Diff)
AS
(
SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)
FROM EmpOrdersCTE
),
NumsCTE(Num)
AS
(
SELECT 1 AS Num
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
),
StepsCTE(Step, Fromval, Toval)
AS
(
SELECT
Num,
MN + ROUND((Num-1)*((Diff+1)/4.), 0),
MN + ROUND((Num)*((Diff+1)/4.), 0) - 1
FROM MinMaxCTE CROSS JOIN NumsCTE
),
HistogramCTE(Step, Fromval, Toval, Samples)
AS
(
SELECT S.Step, S.Fromval, S.Toval, COUNT(*)
FROM StepsCTE AS S
JOIN EmpOrdersCTE AS OE
ON OE.Cnt BETWEEN S.Fromval AND S.Toval
GROUP BY S.Step, S.Fromval, S.Toval
)
SELECT * FROM HistogramCTE
結果セットは以下のようになります。
Step Fromval Toval Samples
----- -------- ------ ---------
1 42 70 3
2 71 99 2
3 100 127 3
4 128 156 1
2 番目の CTE (MinMaxCTE) が最初の CTE (EmpOrdersCTE) を参照しており、3 番目の CTE (NumsCTE) はどの CTE も参照していないことに注目してください。 また、4 番目の CTE (StepsCTE) は 2 番目と 3 番目の CTE を参照しており、5 番目の CTE (HistogramCTE) は最初と 4 番目の CTE を参照しています。
再帰クエリ
非再帰 CTE を使用すると、表現力が増します。 ただし、非再帰 CTE を使用するコードの各部分には、通常、より短いコードを記述できます。その短いコードで、派生テーブルなどの他の Transact-SQL 構造体を使用して同じ結果を得ることができます。 この場合は、再帰 CTE とは異なります。 ここでは、再帰クエリのセマンティクスについて説明し、組織図での従業員の階層、および資料の請求書 (BOM) のシナリオの実用的な実装を提供します。
セマンティクス
CTE が CTE 自体を参照する場合、CTE は再帰していると考えられます。 再帰 CTE は、少なくとも 2 個のクエリ (または再帰クエリ用法のメンバ) で構成されます。 1 つのクエリは、非再帰クエリであり、アンカー メンバ (AM) とも呼ばれています。 もう 1 つのクエリは、再帰クエリで、再帰メンバ (RM) とも呼ばれます。 クエリは、UNION ALL 演算子によって分離されます。 以下の例で、再帰 CTE の簡素化された汎用形式を示します。
WITH RecursiveCTE(<column_list>)
AS
(
-- Anchor Member:
-- SELECT query that does not refer to RecursiveCTE
SELECT ...
FROM <some_table(s)>
...
UNION ALL
-- Recursive Member
-- SELECT query that refers to RecursiveCTE
SELECT ...
FROM <some_table(s)>
JOIN RecursiveCTE
...
)
-- Outer Query
SELECT ...
FROM RecursiveCTE
...
再帰 CTE を実装するアルゴリズムは、以下のように論理的に考えることができます。
-
アンカー メンバがアクティブになります。 SET R0 (R は結果 (Results) を表します) が生成されます。
-
再帰メンバがアクティブになり、RecursiveCTE を参照しているときに、入力として SET Ri (i = 手順番号) を取得します。 SET Ri + 1 が生成されます。
-
手順 2 のロジックは、空の SET が返されるまで、繰り返し実行されます (繰り返されるたびに、手順番号が増加されます)。
-
外部クエリが実行され、RecursiveCTE を参照しているときに、以前の手順すべての累積 (UNION ALL) 結果が取得されます。
CTE には複数のメンバを保持できます。しかし、再帰メンバと他のメンバ (再帰または非再帰) 間に使用できる演算子は、UNION ALL 演算子のみです。 UNION などの他の演算子は、非再帰メンバ間にしか使用できません。 暗黙の変換をサポートする通常の UNION や UNION ALL 演算子とは異なり、再帰 CTE では、同じデータ型、長さ、有効桁数など、すべてのメンバの列が正確に一致する必要があります。
再帰 CTE および再帰ルーチンの従来の開発 (SQL Server に固有である必要なし) の間に共通点を見出したい場合、類似点は見つけることができます。 再帰ルーチンは、通常 3 つの重要な要素で構成されます。3 つの要素とは、ルーチンの初回の呼び出し、再帰的な終了チェック、および同じルーチンへの再帰呼び出しです。 アンカー メンバがルーチンの初回呼び出しに対応し、再帰メンバがルーチンの再帰呼び出しに対応します。また、終了チェックは、再帰ルーチン (たとえば、IF ステートメントを使用) では通常明示的ですが、再帰 CTE では暗黙的です。再帰 CTE では、前の呼び出しから行が返されなかったときに再帰が停止します。
本書の次のセクションでは、1 つおよび複数の親を持つ環境での再帰 CTE の実例と用途を紹介します。
1 つの親を持つ環境 : 従業員組織図
1 つの親を持つ階層のシナリオには、従業員組織図を使用します。
注意 ここでの例では、Northwind の Employees テーブルとは異なる構造を持つ Employees という名前のテーブルを作成および使用します。 コードは、独自のテスト用データベース、または tempdb で実行してください。Northwind は使用しないでください。
以下のコードを実行して、Employees テーブルを生成し、そのテーブルにサンプル データを作成します。
USE tempdb -- or your own test database
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
SET NOCOUNT ON
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
各従業員は、ID が mgrid 列に格納されているマネージャに直属します。 empid 列を参照する mgrid 列に、外部キーが定義されています。つまり、マネージャ ID はテーブル内の有効な従業員 ID に合致するか、NULL である必要があります。 上司の Nancy は、mgrid 列が NULL になります。 マネージャと従業員の関係を図 1 に示します。
.gif)
図 1. 従業員組織図
以下に、Employees テーブルに対する一般的な要求をいくつかを示します。
-
Robert (empid=7) および彼の部下全員 (すべてのレベル) の詳細を教えてください。
-
Janet (empid=3) より下位の 2 レベルに存在するすべての従業員の詳細を教えてください。
-
James (empid=14) までの管理のつながりを教えてください。
-
各マネージャに何人の従業員が直接または間接的に属していますか?
-
階層の従属関係が見やすいように従業員すべてを示してください。
再帰 CTE は、このような要求を処理する手段を提供します。また、再帰 CTE は本質的に再帰的なので、データベースに階層に関する追加情報を保持する必要はありません。
最初の要求は、最も一般的な要求であると考えられます。つまり、従業員 (たとえば、empid=7 の Robert) およびその人物より下位のすべてのレベルに存在する部下を返します。 この要求へのソリューションは、以下の CTE により提供されます。
WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
-- Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 7
UNION ALL
-- Recursive Member (RM)
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE
結果セットは以下のようになります。
empid empname mgrid lvl
----------- ------------------------- ----------- -----------
7 Robert 3 0
11 David 7 1
12 Ron 7 1
13 Dan 7 1
14 James 11 2
この CTE は、前に説明した再帰 CTE ロジックに従って、次のように処理されます。
-
アンカー メンバは、Employees から Robert の行が返されるときにアクティブになります。 lvl 結果列に定数 0 が返されることに注目してください。
-
再帰メンバは、繰り返しアクティブになり、Employees および EmpCTE の結合操作による以前の結果の直属の部下を返します。 以下のように、Employees は部下を表し、EmpCTE (以前の呼び出しの結果を含んでいます) はマネージャを表します。
-
まず、Robert の部下、David、Ron、および James が返されます。
-
次に、David、Ron、および Dan の部下が返されます。つまり、James だけが返されます。
-
最後に、James の部下が返されます。この場合、誰も返されず、再帰が終了されます。
-
外部クエリにより、EmpCTE から全行が返されます。
再帰呼び出しごとに、lvl 値が繰り返しインクリメントされることがわかります。
このレベル カウンタを使用すると、再帰での繰り返し数を制限できます。 たとえば、以下の CTE を使用して、Janet より下位の 2 レベルに存在するすべての従業員を返します。
WITH EmpCTEJanet(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 3
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees as E
JOIN EmpCTEJanet as M
ON E.mgrid = M.empid
WHERE lvl < 2
)
SELECT empid, empname
FROM EmpCTEJanet
WHERE lvl = 2
結果セットは以下のようになります。
empid empname
----------- -------------------------
11 David
12 Ron
13 Dan
以前のコード例と比較してこのコード例に追加された部分は、太字で示してあります。 再帰メンバのフィルタ WHERE lvl < 2 は、再帰終了チェックとして使用されます。lvl = 2 の時点で返される行がないので、再帰が停止します。 外部クエリのフィルタ WHERE lvl = 2 は、レベル 2 までのすべてのレベルを削除するために使用されます。 外部クエリのフィルタ (lvl = 2) は、それ自体で必要な行のみを返すのに論理的には十分であることに注意してください。 再帰メンバのフィルタ (lvl < 2) は、パフォーマンス上の理由で追加されています。これにより、Janet より下位 2 レベルが返された直後に、再帰が停止されます。
上記のとおり、CTE は同一バッチ内で定義されているローカル変数を参照できます。 たとえば、クエリをより汎用的にするには、従業員 ID とレベルに定数ではなく変数を使用できます。
DECLARE @empid AS INT, @lvl AS INT
SET @empid = 3 -- Janet
SET @lvl = 2 -- two levels
WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = @empid
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees as E
JOIN EmpCTE as M
ON E.mgrid = M.empid
WHERE lvl < @lvl
)
SELECT empid, empname
FROM EmpCTE
WHERE lvl = @lvl
再帰を特定回数繰り返し呼び出した後で、ヒントを使用してクエリを強制終了することができます。 この処理を行うには、外部クエリの末尾に OPTION(MAXRECURSION value) を追加します。以下に例を示します。
WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees as E
JOIN EmpCTE as M
ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE
OPTION (MAXRECURSION 2)
結果セットは以下のようになります。
empid empname mgrid lvl
----------- ------------------------- ----------- -----------
1 Nancy NULL 0
2 Andrew 1 1
3 Janet 1 1
4 Margaret 1 1
10 Ina 4 2
7 Robert 3 2
8 Laura 3 2
9 Ann 3 2
.Net SqlClient Data Provider: Msg 530, Level 16, State 1, Line 1
(メッセージ 530、レベル 16、状態 1、行 1)
Statement terminated.(ステートメントは終了されました。)
Maximum recursion 2 has been exhausted before statement completion
(ステートメントを完了する前に、最大再帰 2 に到達しました。)
これまでに生成した結果が返され、エラー 530 が生成されます。 Janet より下位 2 レベルに存在する従業員を返す要求を実装する場合、以下のように、再帰メンバのフィルタではなく、MAXRECURSION ヒントを使用できます。
WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 3
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees as E
JOIN EmpCTE as M
ON E.mgrid = M.empid
)
SELECT empid, empname
FROM EmpCTE
WHERE lvl = 2
OPTION (MAXRECURSION 2)
結果セットは以下のようになります。
empid empname
----------- -------------------------
11 David
12 Ron
13 Dan
.Net SqlClient Data Provider: Msg 530, Level 16, State 1, Line 1
(メッセージ 530、レベル 16、状態 1、行 1)
Statement terminated.(ステートメントは終了されました。)
Maximum recursion 2 has been exhausted before statement completion
(ステートメントを完了する前に、最大再帰 2 に到達しました。)
クライアントが結果の他にエラーも取得することに注意してください。 有効な状況でエラーを返すコードを使用するのは、プログラミングの演習として適切ではありません。 上記のフィルタを使用することをお勧めします。MAXRECURSION ヒントは、必要に応じて、無限ループに陥らないための予防手段として使用することをお勧めします。
このヒントを指定しない場合、SQL Server の既定値は 100 になります。 この値は、巡回再帰呼び出しの疑いがある場合に、予防策として使用できます。 再帰呼び出しの回数を制限しない場合、ヒントの MAXRECURSION を 0 に設定してください。
これまでに説明した再帰の例には、マネージャであるアンカー メンバ、および部下を取得する再帰メンバが含まれています。 James の管理パス (James と彼のすべてのレベルでのマネージャ全員) を返す場合など、一部の要求ではその逆が必要になります。 以下のコードで、この要求への回答を提供します。
WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 14
UNION ALL
SELECT M.empid, M.empname, M.mgrid, E.lvl+1
FROM Employees as M
JOIN EmpCTE as E
ON M.empid = E.mgrid
)
SELECT * FROM EmpCTE
結果セットは以下のようになります。
empid empname mgrid lvl
----------- ------------------------- ----------- -----------
14 James 11 0
11 David 7 1
7 Robert 3 2
3 Janet 1 3
1 Nancy NULL 4
アンカー メンバは James の行を返します。 ここでは 1 つの親を持つ階層を使用しており、要求が 1 人の従業員で開始されるので、再帰メンバには、以前返された従業員のマネージャまたは 1 人のマネージャが返されます。
また、各マネージャに直接または間接的に属している部下の数など、集計を計算する再帰クエリを使用することもできます。
WITH MgrCTE(mgrid, lvl)
AS
(
SELECT mgrid, 0
FROM Employees
WHERE mgrid IS NOT NULL
UNION ALL
SELECT M.mgrid, lvl + 1
FROM Employees AS M
JOIN MgrCTE AS E
ON E.mgrid = M.empid
WHERE M.mgrid IS NOT NULL
)
SELECT mgrid, COUNT(*) AS cnt
FROM MgrCTE
GROUP BY mgrid
結果セットは以下のようになります。
mgrid cnt
----------- -----------
1 13
2 2
3 7
4 1
7 4
11 1
アンカー メンバには、各従業員のマネージャ ID が保持されている行が返されます。 マネージャ ID 列が NULL の場合、特定のマネージャが存在しないことを表すので、NULL は除外されます。 再帰メンバが以前返されたマネージャのマネージャ ID を返します。ここでも、NULL は除外されます。 最終的に、CTE には、各マネージャの直接または間接的な部下の数が保持されます。 外部クエリは、マネージャ ID ごとに結果がグループ化され、その数が返された状態になります。
1 つの親を持つ階層に対する要求の最後の例として、階層の従属関係によって並べ替えまたはインデントを行った Nancy の部下 (上司) を返すとします。 これを行うには、以下のコードを使用します。従業員 ID によって兄弟を並べ替えます。
WITH EmpCTE(empid, empname, mgrid, lvl, sortcol)
AS
(
SELECT empid, empname, mgrid, 0,
CAST(empid AS VARBINARY(900))
FROM Employees
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1,
CAST(sortcol + CAST(E.empid AS BINARY(4)) AS VARBINARY(900))
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT
REPLICATE(' | ', lvl)
+ '(' + (CAST(empid AS VARCHAR(10))) + ') '
+ empname AS empname
FROM EmpCTE
ORDER BY sortcol
(1) Nancy
| (2) Andrew
| | (5) Steven
| | (6) Michael
| (3) Janet
| | (7) Robert
| | | (11) David
| | | | (14) James
| | | (12) Ron
| | | (13) Dan
| | (8) Laura
| | (9) Ann
| (4) Margaret
| | (10) Ina empid 値によって兄弟を並べ替えるには、従業員ごとに sortcol というバイナリ文字列を作成します。 この文字列は、各従業員までの管理チェーンで連結された、バイナリ値に変換された従業員 ID で構成されます。 アンカー メンバは開始点です。 アンカー メンバがルートの従業員の empid を持つバイナリ値を作成します。 反復されるたびに、再帰メンバがバイナリ値に変換された現在の従業員 ID をマネージャの sortcol に追加します。 その後、外部クエリが sortcol で結果を並べ替えます。 アンカー メンバと再帰メンバの両方の対応する列が、同じデータ型、長さ、および有効桁数を保持することに注意してください。 これにより、整数値が 2 進表現で 4 バイト必要な場合でも、sortcol 値を生成する式が varbinary(900) に変換されます。900 バイトは 225 レベルを表します。そのため、この式はより適切な制限であるように見えます。 より多くのレベルをサポートしたい場合、この長さを増やすことが可能ですが、両方のメンバでその操作を行う必要があります。そうしないと、エラーが発生します。
階層的なインデントを行うには、従業員数のレベルと同じ回数、文字列 (この場合は ' | ') を複製します。 そのため、従業員 ID 自体がかっこ内に追加され、最終的には従業員の名前も追加されます。
複数の親を持つ環境 : 部品一覧表
前のセクションでは、CTE を使用して、ツリー内の各ノードに親が 1 つだけ存在する階層を扱いました。 リレーションシップのより複雑なシナリオとして、各ノードに複数の親が存在するグラフがあります。 ここでは、部品一覧表 (BOM) での CTE の使用について説明します。 BOM は、非循環有向グラフで、各ノードに複数の親を保持できます。つまり、ノードを直接または間接的にノード自体の親にすることができず、2 つのノード間のリレーションシップは二次元ではありません (たとえば、A は C を含みますが、C は A を含みません)。 図 2 では、BOM シナリオでの項目間のリレーションシップを示します。
.gif)
図 2. 複数の親を持つ環境
たとえば、項目 A は D、B、および C を含み、項目 C は B および E を含み、項目 B は項目 A と C に含まれます。 以下のコードを実行して、Items および BOM テーブルを作成し、それらのテーブルにサンプル データを作成します。
CREATE TABLE Items
(
itemid VARCHAR(5) NOT NULL PRIMARY KEY,
itemname VARCHAR(25) NOT NULL,
/* other columns, e.g., unit_price, measurement_unit */
)
CREATE TABLE BOM
(
itemid VARCHAR(5) NOT NULL REFERENCES Items,
containsid VARCHAR(5) NOT NULL REFERENCES Items,
qty INT NOT NULL
/* other columns, e.g., quantity */
PRIMARY KEY(itemid, containsid),
CHECK (itemid <> containsid)
)
SET NOCOUNT ON
INSERT INTO Items(itemid, itemname) VALUES('A', 'Item A')
INSERT INTO Items(itemid, itemname) VALUES('B', 'Item B')
INSERT INTO Items(itemid, itemname) VALUES('C', 'Item C')
INSERT INTO Items(itemid, itemname) VALUES('D', 'Item D')
INSERT INTO Items(itemid, itemname) VALUES('E', 'Item E')
INSERT INTO Items(itemid, itemname) VALUES('F', 'Item F')
INSERT INTO Items(itemid, itemname) VALUES('G', 'Item G')
INSERT INTO Items(itemid, itemname) VALUES('H', 'Item H')
INSERT INTO Items(itemid, itemname) VALUES('I', 'Item I')
INSERT INTO Items(itemid, itemname) VALUES('J', 'Item J')
INSERT INTO Items(itemid, itemname) VALUES('K', 'Item K')
INSERT INTO BOM(itemid, containsid, qty) VALUES('E', 'J', 1)
INSERT INTO BOM(itemid, containsid, qty) VALUES('C', 'E', 3)
INSERT INTO BOM(itemid, containsid, qty) VALUES('A', 'C', 2)
INSERT INTO BOM(itemid, containsid, qty) VALUES('H', 'C', 4)
INSERT INTO BOM(itemid, containsid, qty) VALUES('C', 'B', 2)
INSERT INTO BOM(itemid, containsid, qty) VALUES('B', 'F', 1)
INSERT INTO BOM(itemid, containsid, qty) VALUES('B', 'G', 3)
INSERT INTO BOM(itemid, containsid, qty) VALUES('A', 'B', 2)
INSERT INTO BOM(itemid, containsid, qty) VALUES('A', 'D', 2)
INSERT INTO BOM(itemid, containsid, qty) VALUES('H', 'I', 1) Items テーブルには、各項目の行が保持されます。 BOM テーブルには、グラフのノード間のリレーションシップが含まれます。 各リレーションシップは、親の項目 ID (itemid)、子の項目 ID (containsid)、および itemid 内の containsid の数量 (qty) で構成されます。
BOM シナリオの共通の要求は、項目を "解体"することです。 つまり、グラフ全体を、直接または間接的に、特定の項目からスキャン開始し、グラフに含まれるすべての項目を返します。 この処理は、従業員組織図のようなツリーに含まれるサブツリーを返すことに似ているので、理解しやすいかもしれません。 ただし、有向グラフでは、含まれている 1 つの項目に、別の経路に含まれるいくつか異なる項目から到達できるので、要求は概念上少し複雑になります。 たとえば、項目 A を取り出すとします。 項目 A から項目 B にたどり着く 2 つの異なる経路が存在することに気付きます。 2 つの経路とは、A→B および A→C→B です。 つまり、項目 B には 2 回到達します。これは、B に含まれるすべての項目 (F および G) に 2 回到達することを意味します。 幸いにも、CTE を使用すると、このような要求は、ツリーに含まれるサブツリーを取得する要求を実装するのと同じくらい単純に実装できます。以下に例を示します。
WITH BOMCTE
AS
(
SELECT *
FROM BOM
WHERE itemid = 'A'
UNION ALL
SELECT BOM.*
FROM BOM
JOIN BOMCTE
ON BOM.itemid = BOMCTE.containsid
)
SELECT * FROM BOMCTE
結果セットは以下のようになります。
itemid containsid qty
------ ---------- -----------
A B 2
A C 2
A D 2
C B 2
C E 3
E J 1
B F 1
B G 3
B F 1
B G 3
アンカー メンバは、BOM に含まれている A の直接的な項目をすべて返します。 再帰メンバは、CTE の前の反復で返されたときに含まれていた項目ごとに、BOM と BOMCTE を結合することで含まれる項目を返します。 (出力の順序は必ずしもこのとおりではありませんが) 論理的には、まず、(A, B)、(A, C)、(A, D) が返されます。次に、(B, F)、(B, G)、(C, B)、(C, E) が返されます。最後に、(B, F)、(B, G)、(E, J) が返されます。 BOM からの大部分の要求では、最終的な結果に複数回項目を表示する必要がないことに注意してください。 解体で必要な項目のみを表示する場合、DISTINCT 句を使用して重複する項目を除去できます。以下に例を示します。
WITH BOMCTE
AS
(
SELECT *
FROM BOM
WHERE itemid = 'A'
UNION ALL
SELECT BOM.*
FROM BOM
JOIN BOMCTE
ON BOM.itemid = BOMCTE.containsid
)
SELECT DISTINCT containsid FROM BOMCTE
結果セットは以下のようになります。
containsid
----------
B
C
D
E
F
G
J
部品を解体する処理を理解しやすくするために、すべての項目がその各項目に含まれている項目に展開されるツリーで中間結果をビジュアルに示します。 図 3 は、項目の量に従って部分 A と H を展開することによって形成したツリーを示しています。
.gif)
図 3. 部品の展開
元の要求をさらに進めていくと、通常は、項目自体を取得するのではなく、各項目の累積数量を取得することに関心を持つようになります。 たとえば、A が C を 2 個含みます。 C が E を 3 個含みます。 E が J を 1 個含みます。 A に必要な J の合計数は、A から J までのパスに存在する製品の数です。 2*3*1 = 6. 図 4 は、項目を集計する前に A を構成する各項目の累積数量を示しています。
.gif)
図 4. 部品の展開 — 計算された数量
次の CTE は、製品の累積数量を計算します。
WITH BOMCTE(itemid, containsid, qty, cumulativeqty)
AS
(
SELECT *, qty
FROM BOM
WHERE itemid = 'A'
UNION ALL
SELECT BOM.*, BOM.qty * BOMCTE.cumulativeqty
FROM BOM
JOIN BOMCTE
ON BOM.itemid = BOMCTE.containsid
)
SELECT * FROM BOMCTE
結果セットは以下のようになります。
itemid containsid qty cumulativeqty
------ ---------- ----------- -------------
A B 2 2
A C 2 2
A D 2 2
C B 2 4
C E 3 6
E J 1 6
B F 1 4
B G 3 12
B F 1 2
B G 3 6
この CTE は、cumulativeqty 列を前の CTE に追加します。 アンカー メンバは、含まれている項目の数量を cumulativeqty として返します。 再帰メンバが、次のレベルに含まれている項目ごとに、項目の数量とその項目に含まれている項目の累積数量を乗算します。 複数の経路から到達される項目は、複数回結果に表示されることに注意してください。つまり、経路ごとの累積数量が表示されます。 このような出力は、それ自体ではあまり意味がありませんが、各項目が一度だけ表示される最終的な結果への中間ステップを理解するのに役立ちます。 A に含まれる各項目の合計数量を取得するには、以下のように、外部クエリ グループに containsid ごとの結果を保持します。
WITH BOMCTE(itemid, containsid, qty, cumulativeqty)
AS
(
SELECT *, qty
FROM BOM
WHERE itemid = 'A'
UNION ALL
SELECT BOM.*, BOM.qty * BOMCTE.cumulativeqty
FROM BOM
JOIN BOMCTE
ON BOM.itemid = BOMCTE.containsid
)
SELECT containsid AS itemid, SUM(cumulativeqty) AS totalqty
FROM BOMCTE
GROUP BY containsid
結果セットは以下のようになります。
itemid totalqty
------ -----------
B 6
C 2
D 2
E 6
F 6
G 18
J 6
PIVOT および UNPIVOT
PIVOT および UNPIVOT は、クエリの FROM 句で指定する新しいリレーショナル演算子です。 これらのリレーショナル演算子を使用すると、入力テーブル値式で何らかの操作を実行し、出力テーブルに結果を表示できます。 PIVOT 演算子は、行を列に変換し、変換を終えるまで集計を実行します。 指定したピボット列に基づいて入力テーブル式を拡大して、ピボット列内の一意の値それぞれに対する列を持つ出力テーブルを生成します。 UNPIVOT 演算子は、PIVOT 演算子とは逆の操作を実行します。つまり、列を行に変換します。 ピボット テーブル列に基づいて入力テーブル式を絞り込みます。
PIVOT
PIVOT 演算子は、オープン スキーマのシナリオの処理、およびクロス集計レポートの生成に役立ちます。
オープン スキーマのシナリオでは、各エンティティの種類に対して不明または異なる一連の属性を持つエンティティを管理します。 アプリケーションのユーザーは、属性を動的に定義します。 多くの列を事前定義して、テーブルに NULL 値を多く格納するのではなく、属性を異なる行に分割し、エンティティの各インスタンスに関連する属性のみを格納します。
PIVOT を使用すると、オープン スキーマのシナリオや他のシナリオ向けのクロス集計レポートを生成できます。その際、行を列に変換し、変換が終わるまで集計を計算して役立つ形式でデータを表示できます。
オープン スキーマのシナリオの例として、オークションへの品目の売り出しを追跡するデータベースがあります。 品目の種類、品目の作成時期、および品目の初期価格など、一部の属性は、オークションのすべての品目に関連します。 すべての品目に関連する属性のみが AuctionItems テーブルに格納されます。
CREATE TABLE AuctionItems
(
itemid INT NOT NULL PRIMARY KEY NONCLUSTERED,
itemtype NVARCHAR(30) NOT NULL,
whenmade INT NOT NULL,
initialprice MONEY NOT NULL,
/* other columns */
)
CREATE UNIQUE CLUSTERED INDEX idx_uc_itemtype_itemid
ON AuctionItems(itemtype, itemid)
INSERT INTO AuctionItems VALUES(1, N'Wine', 1822, 3000)
INSERT INTO AuctionItems VALUES(2, N'Wine', 1807, 500)
INSERT INTO AuctionItems VALUES(3, N'Chair', 1753, 800000)
INSERT INTO AuctionItems VALUES(4, N'Ring', -501, 1000000)
INSERT INTO AuctionItems VALUES(5, N'Painting', 1873, 8000000)
INSERT INTO AuctionItems VALUES(6, N'Painting', 1889, 8000000)
他の属性は品目の種類に固有なので、別の種類の新しい品目を引き続き追加します。 このような属性は、品目の各属性が別の行に格納される、別の ItemAttributes テーブルに格納できます。 各行には、次のように、品目の ID、属性名、および属性値が含まれます。
CREATE TABLE ItemAttributes
(
itemid INT NOT NULL REFERENCES AuctionItems,
attribute NVARCHAR(30) NOT NULL,
value SQL_VARIANT NOT NULL,
PRIMARY KEY (itemid, attribute)
)
INSERT INTO ItemAttributes
VALUES(1, N'manufacturer', CAST(N'ABC' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(1, N'type', CAST(N'Pinot Noir' AS NVARCHAR(15)))
INSERT INTO ItemAttributes
VALUES(1, N'color', CAST(N'Red' AS NVARCHAR(15)))
INSERT INTO ItemAttributes
VALUES(2, N'manufacturer', CAST(N'XYZ' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(2, N'type', CAST(N'Porto' AS NVARCHAR(15)))
INSERT INTO ItemAttributes
VALUES(2, N'color', CAST(N'Red' AS NVARCHAR(15)))
INSERT INTO ItemAttributes
VALUES(3, N'material', CAST(N'Wood' AS NVARCHAR(15)))
INSERT INTO ItemAttributes
VALUES(3, N'padding', CAST(N'Silk' AS NVARCHAR(15)))
INSERT INTO ItemAttributes
VALUES(4, N'material', CAST(N'Gold' AS NVARCHAR(15)))
INSERT INTO ItemAttributes
VALUES(4, N'inscription', CAST(N'One ring ...' AS NVARCHAR(50)))
INSERT INTO ItemAttributes
VALUES(4, N'size', CAST(10 AS INT))
INSERT INTO ItemAttributes
VALUES(5, N'artist', CAST(N'Claude Monet' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(5, N'name', CAST(N'Field of Poppies' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(5, N'type', CAST(N'Oil' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(5, N'height', CAST(19.625 AS NUMERIC(9,3)))
INSERT INTO ItemAttributes
VALUES(5, N'width', CAST(25.625 AS NUMERIC(9,3)))
INSERT INTO ItemAttributes
VALUES(6, N'artist', CAST(N'Vincent Van Gogh' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(6, N'name', CAST(N'The Starry Night' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(6, N'type', CAST(N'Oil' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(6, N'height', CAST(28.75 AS NUMERIC(9,3)))
INSERT INTO ItemAttributes
VALUES(6, N'width', CAST(36.25 AS NUMERIC(9,3)))
value 列には、sql_variant データ型が使用されることに注意してください。これは、さまざまな属性値がさまざまなデータ型を持つ可能性があるためです。 たとえば、size 属性には整数の属性値が格納され、name 属性には文字列の属性値が格納されます。
絵画 (品目 5 および 6) の各品目の行、および各属性の列を持つ ItemAttributes テーブルからデータを表示するとします。 PIVOT 演算子を使用しない場合、以下のようにクエリを記述する必要があります。
SELECT
itemid,
MAX(CASE WHEN attribute = 'artist' THEN value END) AS [artist],
MAX(CASE WHEN attribute = 'name' THEN value END) AS [name],
MAX(CASE WHEN attribute = 'type' THEN value END) AS [type],
MAX(CASE WHEN attribute = 'height' THEN value END) AS [height],
MAX(CASE WHEN attribute = 'width' THEN value END) AS [width]
FROM ItemAttributes AS ATR
WHERE itemid IN(5,6)
GROUP BY itemid
結果セットは以下のようになります。
itemid artist name type height width
------ ---------------- ---------------- ---------- ------ ------
5 Claude Monet Field of Poppies Oil 19.625 25.625
6 Vincent Van Gogh The Starry Night Oil 28.750 36.250
PIVOT 演算子を使用すると、以下のように、より短くて読みやすいコードで、同じ結果を得ることができます。
SELECT *
FROM ItemAttributes AS ATR
PIVOT
(
MAX(value)
FOR attribute IN([artist], [name], [type], [height], [width])
) AS PVT
WHERE itemid IN(5,6)
最新の機能を理解する場合と同様に、PIVOT 演算子を理解するには、試しに使ってみることです。 PIVOT 構文のいくつかの要素は、見ただけで理解できます。それらの要素と新しい演算子を使用しないクエリのリレーションシップを理解することが必要なだけです。それ以外の要素は明確です。
以下にいくつかの用語を示します。これらの用語は、PIVOT 演算子のセマンティクスを理解するのに役立ちます。
table_expression (テーブル式)
PIVOT 演算子が動作する仮想テーブル (FROM 句および PIVOT 演算子間のクエリの一部) です。上記の場合は、ItemAttributes AS ATR に相当します。
pivot_column (ピボット列)
結果列に変換する値を持つ、table_expression から取得される列です。上記の場合は、attribute に相当します。
column_list (列リスト)
結果列として表示する、pivot_column から取得される値の一覧 (IN 句の後のカッコ内) です。 これらは、有効な識別子として表される必要があります。上記の場合は、[artist]、[name]、[type]、[height]、[width] に相当します。
aggregate_function (集計関数)
結果のデータまたは列の値を生成するために使用する集計関数です。上記の場合は、MAX() に相当します。
value_column (値列)
aggregate_function の引数として使用する、table_expression から取得される列です。上記の場合は、value に相当します。
group_by_list (GROUP BY リスト)
非表示の部分、つまり、結果をグループ化するために使用される、pivot_column および value_column を除外する table_expression から取得されるすべての列です。上記の場合は、itemid に相当します。
select_list (選択リスト)
SELECT 句に基づいた列の一覧で、group_by_list および column_list からの列を含めることができます。 別名を使用して、結果列の名前を変更できます。上記の場合は、* に相当し、group_by_list および column_list に含まれるすべての列を返します。
PIVOT 演算子は、GROUP BY 句を含むクエリがあり、それらの列を指定したかのように、group_by_list の一意の値ごとに 1 行を返します。 group_by_list がクエリ内のどの場所にも明示的に指定されていないことがわかります。これは、暗黙的といえます。 table_expression から、pivot_column および value_column 以外のすべての列が含まれます。 これを理解することは、PIVOT 演算子を使用して記述したクエリが動作する理由、および一部のケースでエラーになる理由を理解するのに重要であると考えられます。
結果列には、group_by_list および <column_list> からの値が含まれると考えられます。 アスタリスク (*) を指定すると、クエリにより両方の一覧が返されます。 結果列のデータ部分または結果列の値が、引数として value_column を使用する aggregate_function によって計算されます。
以下のコードは、PIVOT 演算子を使用するクエリ内のさまざまな要素を例示しています。
SELECT * -- itemid, [artist], [name], [type], [height], [width]
FROM ItemAttributes AS ATR
PIVOT
(
MAX(value)
FOR attribute IN([artist], [name], [type], [height], [width])
) AS PVT
WHERE itemid IN(5,6)
以下のコードは、PIVOT 演算子を使用しないクエリにさまざまな要素を関連付けています。
SELECT
itemid,
MAX(CASE WHEN attribute = 'artist' THEN value END) AS [artist],
MAX(CASE WHEN attribute = 'name' THEN value END) AS [name],
MAX(CASE WHEN attribute = 'type' THEN value END) AS [type],
MAX(CASE WHEN attribute = 'height' THEN value END) AS [height],
MAX(CASE WHEN attribute = 'width' THEN value END) AS [width]
FROM ItemAttributes AS ATR
WHERE itemid IN(5,6)
GROUP BY itemid
<column_list> には、値を明示的に指定する必要があることに注意してください。 PIVOT 演算子には、静的クエリの pivot_column から動的に値を派生するオプションは用意されていません。 この処理を行うには、動的 SQL を使用して、クエリ文字列をユーザー自身で作成します。
前の PIVOT クエリをさらに進めて、各オークション品目の絵画に関連するすべての属性を返し、AuctionItems に存在する属性と、ItemAttributes に存在する属性を含めるとします。 以下のクエリを実行してみると、エラーが返されます。
SELECT *
FROM AuctionItems AS ITM
JOIN ItemAttributes AS ATR
ON ITM.itemid = ATR.itemid
PIVOT
(
MAX(value)
FOR attribute IN([artist], [name], [type], [height], [width])
) AS PVT
WHERE itemtype = 'Painting'
以下のエラー メッセージが表示されます。
.Net SqlClient Data Provider: Msg 8156, Level 16, State 1, Line 1
(メッセージ 8156、レベル 16、状態 1、行 1)
The column 'itemid' was specified multiple times for 'PVT'.
(列名 'itemid' は 'PVT' に対して複数回指定されています。)
.Net SqlClient Data Provider: Msg 107, Level 15, State 1, Line 1
(メッセージ 107、レベル 15、状態 1、行 1)
The column prefix 'ITM' does not match with a table name or alias name used in the query.
(列のプレフィックス 'ITM' は、テーブル名と一致しないか、クエリ内の別名と一致しません。)
PIVOT は、table_expression で動作し、FROM 句と PIVOT 句間のクエリのセクションによって返される仮想テーブルであることを覚えておいてください。 このクエリでは、仮想テーブルに itemid 列の 2 つのインスタンスが含まれています。1 つは AuctionItems で、もう 1 つは ItemAttributes で作成されたインスタンスです。 以下のようにクエリを変更できますが、この場合もエラーが発生します。
SELECT ITM.itemid, itemtype, whenmade, initialprice,
[artist], [name], [type], [height], [width]
FROM AuctionItems AS ITM
JOIN ItemAttributes AS ATR
ON ITM.itemid = ATR.itemid
PIVOT
(
MAX(value)
FOR attribute IN([artist], [name], [type], [height], [width])
) AS PVT
WHERE itemtype = 'Painting'
以下のエラー メッセージが表示されます。
.Net SqlClient Data Provider: Msg 8156, Level 16, State 1, Line 1 (メッセージ 8156、レベル 16、状態 1、行 1) The column 'itemid' was specified multiple times for 'PVT'. (列名 'itemid' は 'PVT' に対して複数回指定されています。) .Net SqlClient Data Provider: Msg 107, Level 15, State 1, Line 1 (メッセージ 107、レベル 15、状態 1、行 1) The column prefix 'ITM' does not match with a table name or alias name used in the query. (列のプレフィックス 'ITM' は、テーブル名と一致しないか、クエリ内の別名と一致しません。)
上記のとおり、PIVOT 演算子は、select_list の列ではなく、table_expression によって返される仮想テーブル上で動作します。 select_list は、PIVOT 演算子がその操作を実行した後で評価され、group_by_list および column_list のみを参照できます。 それが理由で、別名 ITM は select_list で認識されなくなります。 これを理解すると、動作する列のみを含む table_expression に PIVOT を指定する必要があることに気付きます。 これには、グループ列 (itemid と itemtype、whenmade、およびinitialprice の 1 つだけ存在)、ピボット列 (attribute)、および値列 (value) が含まれます。 CTE または派生テーブルを使用して、これを実現できます。 以下に、CTE を使用する例を示します。
WITH PNT
AS
(
SELECT ITM.*, ATR.attribute, ATR.value
FROM AuctionItems AS ITM
JOIN ItemAttributes AS ATR
ON ITM.itemid = ATR.itemid
WHERE ITM.itemtype = 'Painting'
)
SELECT * FROM PNT
PIVOT
(
MAX(value)
FOR attribute IN([artist], [name], [type], [height], [width])
) AS PVT
結果セットは以下のようになります。
itemid itemtype whenmade initialprice artist name type height width
------ -------- -------- ------------ ---------------- ---------------- ---- ------ -----
5 Painting 1873 8000000.0000 Claude Monet Field of Poppies Oil 19.62 25.62
6 Painting 1889 8000000.0000 Vincent Van Gogh The Starry Night Oil 28.75 36.25
以下に、派生テーブルを使用する例を示します。
SELECT *
FROM (SELECT ITM.*, ATR.attribute, ATR.value
FROM AuctionItems AS ITM
JOIN ItemAttributes AS ATR
ON ITM.itemid = ATR.itemid
WHERE ITM.itemtype = 'Painting') AS PNT
PIVOT
(
MAX(value)
FOR attribute IN([artist], [name], [type], [height], [width])
) AS PVT
また、クロス集計レポートを生成してデータを要約するときに、PIVOT を使用することもできます。 たとえば、Northwind データベースの Orders テーブルを使用して、列に従業員 ID をピボットし、各従業員が担当する顧客ごとの注文数を返すとします。 PIVOT 演算子を関連データのみで指定する必要があるのと関係して、派生テーブルを使用して次のクエリを記述します。
SELECT CustomerID,
[1] AS Emp1, [2] AS Emp2, [3] AS Emp3, [4] AS Emp4, [5] AS Emp5,
[6] AS Emp6, [7] AS Emp7, [8] AS Emp8, [9] AS Emp9
FROM (SELECT OrderID, CustomerID, EmployeeID
FROM Orders) ORD
PIVOT
(
COUNT(OrderID)
FOR EmployeeID IN([1], [2], [3], [4], [5], [6], [7], [8], [9])
) AS PVT
結果セットは以下のようになります。
CustomerID Emp1 Emp2 Emp3 Emp4 Emp5 Emp6 Emp7 Emp8 Emp9
---------- ---- ---- ---- ---- ---- ---- ---- ---- ----
ALFKI 2 0 1 2 0 1 0 0 0
ANATR 0 0 2 1 0 0 1 0 0
ANTON 1 0 3 1 0 0 2 0 0
AROUT 3 0 2 4 0 1 0 1 2
BERGS 4 1 6 1 2 0 0 2 2
BLAUS 0 0 1 1 0 1 0 1 3
COUNT(OrderID) 関数は、一覧内の従業員ごとの行数をカウントします。 PIVOT では COUNT(*) を使用できないことに注意してください。 列の別名を使用して、結果列により理解しやすい名前を指定します。 従業員が少数で、従業員 ID が事前にわかっている場合、PIVOT を使用して別の列に含まれる各従業員の注文数を表示するのが合理的です。
また、式から派生した値のピボットを行うこともできます。 たとえば、年を列にピボットして、注文年ごとに、各従業員の合計運送料の値を返すとします。 注文年は、以下のように、OrderDate 列から派生します。
SELECT EmployeeID, [1996] AS Y1996, [1997] AS Y1997, [1998] AS Y1998
FROM (SELECT EmployeeID, YEAR(OrderDate) AS OrderYear, Freight
FROM Orders) AS ORD
PIVOT
(
SUM(Freight)
FOR OrderYear IN([1996], [1997], [1998])
) AS PVT
結果セットは以下のようになります。
EmployeeID Y1996 Y1997 Y1998
----------- ------------------------- ------------------------- ---------
8 1258.4000 2935.2800 3294.2000
4 1968.5900 6648.6000 2728.9500
3 880.0300 6918.3400 3086.3700
7 664.3200 3240.6600 2760.4600
6 766.1000 2114.1700 900.2000
2 973.1800 3796.4100 3926.8200
9 532.8400 1046.0900 1747.3300
5 1365.3700 1184.7500 1368.5900
1 1871.0400 4584.4700 2381.1300
データ ウェアハウスのシナリオでは、クロス集計レポートを使用するのが一般的です。 次の OrdersFact テーブルを考えてみましょう。このテーブルには、Northwind から注文データと注文の詳細データが挿入されます。
CREATE TABLE OrdersFact
(
OrderID INT NOT NULL,
ProductID INT NOT NULL,
CustomerID NCHAR(5) NOT NULL,
EmployeeID INT NOT NULL,
OrderYear INT NOT NULL,
OrderMonth INT NOT NULL,
OrderDay INT NOT NULL,
Quantity INT NOT NULL,
PRIMARY KEY(OrderID, ProductID)
)
INSERT INTO OrdersFact
SELECT O.OrderID, OD.ProductID, O.CustomerID, O.EmployeeID,
YEAR(O.OrderDate) AS OrderYear, MONTH(O.OrderDate) AS OrderMonth,
DAY(O.OrderDate) AS OrderDay, OD.Quantity
FROM Northwind.dbo.Orders AS O
JOIN Northwind.dbo.[Order Details] AS OD
ON O.OrderID = OD.OrderID
行の年と列の月を返して、各年と月の合計数量を取得するには、次のクエリを使用します。
SELECT *
FROM (SELECT OrderYear, OrderMonth, Quantity
FROM OrdersFact) AS ORD
PIVOT
(
SUM(Quantity)
FOR OrderMonth IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS PVT
結果セットは以下のようになります。
OrderYear 1 2 3 4 5 6 7 8 9 10 11 12
--------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
1997 2401 2132 1770 1912 2164 1635 2054 1861 2343 2679 1856 2682
1996 NULL NULL NULL NULL NULL NULL 1462 1322 1124 1738 1735 2200
1998 3466 3115 4065 4680 921 NULL NULL NULL NULL NULL NULL NULL
PIVOT は、年と月の交差部分が存在しない場合は NULL 値を返します。 年は、指定した月のいずれかとの交差部分があるかどうかに関係なく、入力テーブル式 (派生テーブル ORD) に表示されれば、結果に表示されます。 つまり、既存の月をすべて指定しない場合に、すべての列で値が NULL の行を取得する場合があります。 ただし、結果の NULL 値は、交差部分が存在しない場合には表示する必要がありません。 列が NULL 値を許可しない場合を除いては、数量列にベースの NULL 値が含まれる結果となる場合があります。 NULL を上書きして、代わりに別の値 (0 など) を表示する場合、SELECT リストに ISNULL() 関数を使用することで、その処理を行うことができます。
SELECT OrderYear,
ISNULL([1], 0) AS M01,
ISNULL([2], 0) AS M02,
ISNULL([3], 0) AS M03,
ISNULL([4], 0) AS M04,
ISNULL([5], 0) AS M05,
ISNULL([6], 0) AS M06,
ISNULL([7], 0) AS M07,
ISNULL([8], 0) AS M08,
ISNULL([9], 0) AS M09,
ISNULL([10], 0) AS M10,
ISNULL([11], 0) AS M11,
ISNULL([12], 0) AS M12
FROM (SELECT OrderYear, OrderMonth, Quantity
FROM OrdersFact) AS ORD
PIVOT
(
SUM(Quantity)
FOR OrderMonth IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS PVT
結果セットは以下のようになります。
OrderYear M01 M02 M03 M04 M05 M06 M07 M08 M09 M10 M11 M12
--------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
1997 2401 2132 1770 1912 2164 1635 2054 1861 2343 2679 1856 2682
1996 0 0 0 0 0 0 1462 1322 1124 1738 1735 2200
1998 3466 3115 4065 4680 921 0 0 0 0 0 0 0
派生テーブル内で ISNULL(Quantity, 0) を使用する場合、存在しない交差部分に PIVOT が生成した NULL 値ではなく、Quantity 列のベースの NULL 値 (これが存在する場合) のみを処理します。
各年度の第 1 四半期の年と月の値の組み合わせごとに、各従業員の合計数量を返すとします。 行に含まれる年と月の値、および列に含まれる従業員 ID を取得するには、次のクエリを使用します。
SELECT *
FROM (SELECT EmployeeID, OrderYear, OrderMonth, Quantity
FROM OrdersFact
WHERE OrderMonth IN(1,2,3)) AS ORD
PIVOT
(
SUM(Quantity)
FOR EmployeeID IN([1],[2],[3],[4],[5],[6],[7],[8],[9])
) AS PVT
結果セットは以下のようになります。
OrderYear OrderMonth 1 2 3 4 5 6 7 8 9
--------- ----------- ---- ---- ---- ---- ---- ---- ---- ---- ----
1997 1 304 230 364 812 NULL 64 248 305 74
1998 1 397 252 745 586 344 97 265 543 237
1997 2 168 36 574 675 NULL 117 145 417 NULL
1998 2 566 290 662 554 276 129 328 13 297
1997 3 275 151 198 267 188 75 191 288 137
1998 3 467 558 578 481 157 288 307 912 317
EmployeeID および Quantity がそれぞれピボット列と値列として使用されるので、この場合の暗黙的な group-by リストは、OrderYear および OrderMonth です。
ただし、年と月の値の組み合わせを列として表示する場合、ピボット列は 1 つしか存在できないので、PIVOT 演算子にそれらの組み合わせを渡す前に、年と月の値を連結する必要があります。
SELECT *
FROM (SELECT EmployeeID, OrderYear*100+OrderMonth AS YM, Quantity
FROM OrdersFact
WHERE OrderMonth IN(1,2,3)) AS ORD
PIVOT
(
SUM(Quantity)
FOR YM IN([199701],[199702],[199703],[199801],[199802],[199803])
) AS PVT
結果セットは以下のようになります。
EmployeeID 199701 199702 199703 199801 199802 199803
----------- ---------- ---------- ---------- ---------- ---------- -----
1 304 168 275 397 566 467
2 230 36 151 252 290 558
3 364 574 198 745 662 578
4 812 675 267 586 554 481
5 NULL NULL 188 344 276 157
6 64 117 75 97 129 288
7 248 145 191 265 328 307
8 305 417 288 543 13 912
9 74 NULL 137 237 297
UNPIVOT
UNPIVOT 演算子により、ピボット化されていないデータを正規化できます。 UNPIVOT 演算子の構文と要素は、PIVOT 演算子の構文と要素に似ています。
一例として、上記のセクションの AuctionItems テーブルを考えてみましょう。
itemid itemtype whenmade initialprice
----------- ------------------------ ----------- --------------
1 Wine 1822 3000.0000
2 Wine 1807 500.0000
3 Chair 1753 800000.0000
4 Ring -501 1000000.0000
5 Painting 1873 8000000.0000
6 Painting 1889 8000000.0000
ここで、以下の ItemAttributes テーブルに保持されているのと同様の方法で、各属性を異なる行に表示するとします。
itemid attribute value
----------- --------------- -------
1 itemtype Wine
1 whenmade 1822
1 initialprice 3000.00
2 itemtype Wine
2 whenmade 1807
2 initialprice 500.00
3 itemtype Chair
3 whenmade 1753
3 initialprice 800000.00
4 itemtype Ring
4 whenmade -501
4 initialprice 1000000.00
5 itemtype Painting
5 whenmade 1873
5 initialprice 8000000.00
6 itemtype Painting
6 whenmade 1889
6 initialprice 8000000.00
UNPIVOT クエリでは、列 itemtype、whenmade、および initialprice を行に変換したいと考えます。 各行には、項目 ID、属性、および値が含まれる必要があります。 指定する必要のある新しい列名は、attribute と value です。 これらの列は、PIVOT 演算子の pivot_column と value_column に対応します。 attribute 列は、変換する実際の列名 (itemtype、whenmade、および initialprice) を値として取得する必要があります。 value 列は、3 つの変換元の列から 1 つの変換先の列に値を取得する必要があります。 わかりやすくするために、まず、無効な UNPIVOT クエリを示し、次に、一部制限が適用される有効なクエリを示します。
SELECT itemid, attribute, value
FROM AuctionItems
UNPIVOT
(
value FOR attribute IN([itemtype], [whenmade], [initialprice])
) AS UPV
PIVOT 演算子の引数と同様に、FOR 句の前に value_column の名前 (この場合は、value) を指定します。 FOR 句に続いて pivot_column の名前 (この場合は、attribute)を指定し、次に、pivot_column の値として取得する変換元列名のリストを保持する IN 句を指定します。 列のリストは、PIVOT 演算子では <column_list> と呼ばれます。 このクエリは、以下のエラーを発生します。
.Net SqlClient Data Provider: Msg 8167, Level 16, State 1, Line 1(メッセージ 8167、レベル 16、状態 1、行 1)
Type of column 'whenmade' conflicts with the type of other columns specified in the UNPIVOT list.(列 'whenmade' の型が UNPIVOT 一覧で指定された他の列の型と競合しています。)
変換先の value 列には、いくつか異なる変換元列 (これらの列は <column_list> で示されます) からの値が含まれます。 すべての列値の対象が単一の列なので、UNPIVOT では <column_list> のすべての列が同じデータ型、長さ、および有効桁数を持つ必要があります。 この制限を満たすために、3 つの列を同じデータ型に変換するテーブル式を UNPIVOT 演算子に指定できます。 異なる変換元列を同じデータ型に変換し、かつ、元の値を保持できるので、sql_variant データ型がこの場合に使用する適切な候補になります。 この制限を適用することによって上記のクエリを以下のように改定し、目的の結果を得ることができます。
SELECT itemid, attribute, value
FROM (SELECT itemid,
CAST(itemtype AS SQL_VARIANT) AS itemtype,
CAST(whenmade AS SQL_VARIANT) AS whenmade,
CAST(initialprice AS SQL_VARIANT) AS initialprice
FROM AuctionItems) AS ITM
UNPIVOT
(
value FOR attribute IN([itemtype], [whenmade], [initialprice])
) AS UPV
結果の attribute 列のデータ型は sysname になります。 これは、SQL Server がオブジェクト名を格納するのに使用するデータ型です。
UNPIVOT 演算子は、結果から value 列の NULL 値を取り除くので、PIVOT 演算子とまったく逆の演算が行われるとは考えないでください。
AuctionItems の列を行に変換することによって、ひとまとまりの結果を提供するために、UNPIVOT 演算の結果と ItemAttributes テーブルからの行の和集合 (ユニオン) を作成できるようになります。
SELECT itemid, attribute, value
FROM (SELECT itemid,
CAST(itemtype AS SQL_VARIANT) AS itemtype,
CAST(whenmade AS SQL_VARIANT) AS whenmade,
CAST(initialprice AS SQL_VARIANT) AS initialprice
FROM AuctionItems) AS ITM
UNPIVOT
(
value FOR attribute IN([itemtype], [whenmade], [initialprice])
) AS UPV
UNION ALL
SELECT *
FROM ItemAttributes
ORDER BY itemid, attribute
結果セットは以下のようになります。
itemid attribute value
----------- --------------- -------------
1 color Red
1 initialprice 3000.00
1 itemtype Wine
1 manufacturer ABC
1 type Pinot Noir
1 whenmade 1822
2 color Red
2 initialprice 500.00
2 itemtype Wine
2 manufacturer XYZ
2 type Porto
2 whenmade 1807
3 initialprice 800000.00
3 itemtype Chair
3 material Wood
3 padding Silk
3 whenmade 1753
4 initialprice 1000000.00
4 inscription One ring
4 itemtype Ring
4 material Gold
4 size 10
4 whenmade -501
5 height 19.625
5 initialprice 8000000.00
5 itemtype Painting
5 name Field of Poppies
5 artist Claude Monet
5 type Oil
5 whenmade 1873
5 width 25.625
6 height 28.750
6 initialprice 8000000.00
6 itemtype Painting
6 name The Starry Night
6 artist Vincent Van Gogh
6 type Oil
6 whenmade 1889
6 width 36.250
APPLY
APPLY リレーショナル演算子を使用して、外部テーブル式の行ごとに一度、指定されたテーブル値関数を呼び出すことができます。 JOIN リレーショナル演算子を使用するのと同様の方法で、クエリの FROM 句で APPLY を指定します。 APPLY には、CROSS APPLY と OUTER APPLY の 2 つの形式があります。 SQL Server "Yukon" Beta 1 では、APPLY 演算子により、相関サブクエリのテーブル値関数機能を参照できます。
CROSS APPLY
CROSS APPLY は、外部テーブル式の行ごとにテーブル値関数を呼び出します。 テーブル値関数の引数として外部テーブルの列を参照できます。 CROSS APPLY では、テーブル値関数の個別の呼び出しから返されたすべての結果から成る統一された結果セットが返されます。 テーブル値関数により、特定の外部行から空のセットが返される場合、その外部行は結果に返されません。 たとえば、以下のテーブル値関数は、2 つの整数値を受け取り、列として最大値と最小値を持つ、1 行のテーブルを 1 つ返します。
CREATE FUNCTION dbo.fn_scalar_min_max(@p1 AS INT, @p2 AS INT) RETURNS TABLE
AS
RETURN
SELECT
CASE
WHEN @p1 < @p2 THEN @p1
WHEN @p2 < @p1 THEN @p2
ELSE COALESCE(@p1, @p2)
END AS mn,
CASE WHEN @p1 > @p2 THEN @p1
WHEN @p2 > @p1 THEN @p2
ELSE COALESCE(@p1, @p2)
END AS mx
GO
SELECT * FROM fn_scalar_min_max(10, 20)結果セットは以下のようになります。
mn mx
----------- -----------
10 20
以下のような T1 テーブルがあるとします。
CREATE TABLE T1
(
col1 INT NULL,
col2 INT NULL
)
INSERT INTO T1 VALUES(10, 20)
INSERT INTO T1 VALUES(20, 10)
INSERT INTO T1 VALUES(NULL, 30)
INSERT INTO T1 VALUES(40, NULL)
INSERT INTO T1 VALUES(50, 50)
T1 の行ごとに fn_scalar_min_max を呼び出すとします。 その場合、以下のような CROSS APPLY クエリを記述できます。
SELECT *
FROM T1 CROSS APPLY fn_scalar_min_max(col1, col2) AS M
結果セットは以下のようになります。
col1 col2 mn mx
----------- ----------- ----------- -----------
10 20 10 20
20 10 10 20
NULL 30 30 30
40 NULL 40 40
50 50 50 50
テーブル値関数により、特定の外部行に対して複数の行が返される場合、その外部行が複数回返されます。 再帰クエリのセクション (従業員組織図の例) で使用した Employees テーブルを考えます。 同じデータベースに以下の Departments テーブルも作成します。
CREATE TABLE Departments
(
deptid INT NOT NULL PRIMARY KEY,
deptname VARCHAR(25) NOT NULL,
deptmgrid INT NULL REFERENCES Employees
)
SET NOCOUNT ON
INSERT INTO Departments VALUES(1, 'HR', 2)
INSERT INTO Departments VALUES(2, 'Marketing', 7)
INSERT INTO Departments VALUES(3, 'Finance', 8)
INSERT INTO Departments VALUES(4, 'R&D', 9)
INSERT INTO Departments VALUES(5, 'Training', 4)
INSERT INTO Departments VALUES(6, 'Gardening', NULL)
多くの部門は、Employees テーブルの従業員に対応するマネージャ ID を所持します。ただし、Gardening 部門のように、マネージャを持たない部門もあります。 この場合、Employees テーブルのマネージャが、必然的に部門を管理することになります。 以下のテーブル値関数は、従業員 ID を引数として受け取り、その従業員と、その従業員のあらゆるレベルの部下をすべて返します。
CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) RETURNS @TREE TABLE
(
empid INT NOT NULL,
empname VARCHAR(25) NOT NULL,
mgrid INT NULL,
lvl INT NOT NULL
)
AS
BEGIN
WITH Employees_Subtree(empid, empname, mgrid, lvl)
AS
(
-- Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM employees
WHERE empid = @empid
UNION all
-- Recursive Member (RM)
SELECT e.empid, e.empname, e.mgrid, es.lvl+1
FROM employees AS e
JOIN employees_subtree AS es
ON e.mgrid = es.empid
)
INSERT INTO @TREE
SELECT * FROM Employees_Subtree
RETURN
END
GO
各部門のマネージャのあらゆるレベルの部下をすべて返すには、以下のクエリを使用します。
SELECT *
FROM Departments AS D
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST
結果セットは以下のようになります。
deptid deptname deptmgrid empid empname mgrid lvl
----------- ---------- ----------- ----------- ---------- ----------- ---
1 HR 2 2 Andrew 1 0
1 HR 2 5 Steven 2 1
1 HR 2 6 Michael 2 1
2 Marketing 7 7 Robert 3 0
2 Marketing 7 11 David 7 1
2 Marketing 7 12 Ron 7 1
2 Marketing 7 13 Dan 7 1
2 Marketing 7 14 James 11 2
3 Finance 8 8 Laura 3 0
4 R&D 9 9 Ann 3 0
5 Training 4 4 Margaret 1 0
5 Training 4 10 Ina 4
ここでは、注意する点が 2 つあります。 まず、部門のマネージャの fn_getsubtree から複数の行が返されるので、Departments からの各行が重複しています。 次に、Gardening 部門では fn_getsubtree が空のセットを返すので、この部門が結果セットに表示されません。
もう 1 つの CROSS APPLY 演算子の実際的な使い方は、グループごとに n 行を返すことを希望する一般的な要求に答えることです。 一例として、特定の顧客の最近の注文を指定した数だけ返す以下の関数を考えます。
USE Northwind
GO
CREATE FUNCTION fn_getnorders(@custid AS NCHAR(5), @n AS INT)
RETURNS TABLE
AS
RETURN
SELECT TOP(@n) *
FROM Orders
WHERE CustomerID = @custid
ORDER BY OrderDate DESC
GO
CROSS APPLY 演算子を使用することにより、以下の簡単なクエリで、顧客ごとに最近の 2 つの注文を取得できます。
SELECT O.*
FROM Customers AS C
CROSS APPLY fn_getnorders(C.CustomerID, 2) AS O
TOP の機能拡張の詳細については、この資料の後半の「TOP の機能強化」を参照してください。
OUTER APPLY
OUTER APPLY は CROSS APPLY によく似ていますが、テーブル値関数により空のセットが返された外部テーブルの行も返されます。 NULL 値は、テーブル値関数の列に対応する列の値として返されます。 一例として、上記のセクションの Departments テーブルに対するクエリを変更し、CROSS APPLY の代わりに OUTER APPLY を使用します。出力の最終行に注目してください。
SELECT *
FROM Departments AS D
OUTER APPLY fn_getsubtree(D.deptmgrid) AS ST
結果セットは以下のようになります。
deptid deptname deptmgrid empid empname mgrid lvl
----------- ---------- ----------- ----------- ---------- ----------- ---
1 HR 2 2 Andrew 1 0
1 HR 2 5 Steven 2 1
1 HR 2 6 Michael 2 1
2 Marketing 7 7 Robert 3 0
2 Marketing 7 11 David 7 1
2 Marketing 7 12 Ron 7 1
2 Marketing 7 13 Dan 7 1
2 Marketing 7 14 James 11 2
3 Finance 8 8 Laura 3 0
4 R&D 9 9 Ann 3 0
5 Training 4 4 Margaret 1 0
5 Training 4 10 Ina 4 1
6 Gardening NULL NULL NULL NULL
相関サブクエリでのテーブル値関数の使用
SQL Server 2000 では、相関サブクエリ内でテーブル値関数を参照できません。 APPLY リレーショナル演算子の提供に関連して、この制限が取り除かれます。 外部クエリからの列を引数として使用して、サブクエリ内でテーブル値関数を指定できるようになりました。 たとえば、少なくとも 3 人の従業員を部下として持つマネージャがいる部門だけを返す場合、以下のクエリを記述できます。
SELECT *
FROM Departments AS D
WHERE (SELECT COUNT(*)
FROM fn_getsubtree(D.deptmgrid)) >= 3
deptid deptname deptmgrid
----------- ------------------------- -----------
1 HR 2
2 Marketing 7
新しい宣言参照整合性 (DRI) 操作の SET DEFAULT と SET NULL のサポート
ANSI SQL では、FOREIGN KEY 制約のサポートに、4 つの可能な参照操作が定義されています。 外部キーによって参照されるテーブルに対する DELETE 操作または UPDATE 操作への対応としてシステムが反応する方法を示すために、この 4 つの操作を指定します。 SQL Server 2000 は、このような 4 つの操作のうち NO ACTION と CASCADE の 2 つの操作をサポートします。 SQL Server "Yukon" Beta 1 では、SET DEFAULT と SET NULL の 2 つの参照操作に対するサポートが追加されます。
SET DEFAULT 参照操作および SET NULL 参照操作は、DRI 機能を拡張します。 これらのオプションを、外部キー宣言の ON UPDATE 句や ON DELETE 句と連携して使用します。 SET DEFAULT は、参照されるテーブルで行を削除 (ON DELETE) または参照されるキーを更新 (ON UPDATE) するときに、参照しているテーブルの関連行の参照している列値に SQL Server がその列の既定値を設定することを意味します。 同様に、SET NULL オプションを使用すると、参照している列が NULL 値を許容する場合に、SQL Server はそれに反応して値に NULL を設定できます。
たとえば、以下の Customers テーブルには、3 人の実在する顧客と 1 人のダミーの顧客が含まれています。
CREATE TABLE Customers
(
customerid CHAR(5) NOT NULL,
/* other columns */
CONSTRAINT PK_Customers PRIMARY KEY(customerid)
)
INSERT INTO Customers VALUES('DUMMY')
INSERT INTO Customers VALUES('FRIDA')
INSERT INTO Customers VALUES('GNDLF')
INSERT INTO Customers VALUES('BILLY') Orders テーブルは注文を監視します。 注文は、必ずしも実在の顧客に割り当てられる必要はありません。 注文を入力し、顧客 ID を指定しないと、既定では、その注文は DUMMY 顧客 ID に割り当てられます。 Customers テーブルからレコードが削除されるときに、SQL Server が Orders テーブルの関連行の customerid 列に NULL を設定することを希望するとします。 customerid 列に NULL を持つ注文は、"孤立する" ことになります。つまり、その注文はどの顧客にも所属しません。 Customers テーブルの customerid 列への更新も許可するとします。 Orders テーブルの関連行に連鎖更新を行いたいと考えますが、企業のビジネス ルールではそれが許可されておらず、変更された顧客 ID に所属する注文は、既定の顧客 (DUMMY) に関連付ける必要があります。 Customers テーブルの customerid 列を更新するときに、SQL Server が Orders テーブルの関連顧客 ID (customerid) に既定値 'DUMMY' を設定することを希望します。 この場合、以下のような外部キーを持つ Orders テーブルを作成し、いくつか注文を設定します。
CREATE TABLE Orders
(
orderid INT NOT NULL,
customerid CHAR(5) NULL DEFAULT('DUMMY'),
orderdate DATETIME NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY(orderid),
CONSTRAINT FK_Orders_Customers
FOREIGN KEY(customerid)
REFERENCES Customers(customerid)
ON DELETE SET NULL
ON UPDATE SET DEFAULT
)
INSERT INTO Orders VALUES(10001, 'FRIDA', '20040101')
INSERT INTO Orders VALUES(10002, 'FRIDA', '20040102')
INSERT INTO Orders VALUES(10003, 'BILLY', '20040101')
INSERT INTO Orders VALUES(10004, 'BILLY', '20040103')
INSERT INTO Orders VALUES(10005, 'GNDLF', '20040104')
INSERT INTO Orders VALUES(10006, 'GNDLF', '20040105') SET NULL オプションと SET DEFAULT オプションをテストするには、以下の DELETE ステートメントと UPDATE ステートメントを発行します。
DELETE FROM Customers
WHERE customerid = 'FRIDA'
UPDATE Customers
SET customerid = 'DOLLY'
WHERE customerid = 'BILLY'
その結果、以下のように FRIDA の注文の customerid 列には NULL 値が割り当てられ、BILLY の注文には DUMMY が割り当てられます。
orderid customerid orderdate
----------- ---------- ----------------------
10001 NULL 1/1/2004 12:00:00 AM
10002 NULL 1/2/2004 12:00:00 AM
10003 DUMMY 1/1/2004 12:00:00 AM
10004 DUMMY 1/3/2004 12:00:00 AM
10005 GNDLF 1/4/2004 12:00:00 AM
10006 GNDLF 1/5/2004 12:00:00 AM
SET DEFAULT オプションを使用し、参照している列が NULL 以外の既定値を持ち、その既定値が参照されるテーブルに対応する値を持たない場合は、トリガとなる操作を実行したときに、エラーが発生します。 たとえば、Customers から DUMMY 顧客を削除し、次に、GNDLF の customerid を GLDRL に更新すると、エラーが発生します。 UPDATE が SET DEFAULT 操作のトリガとなり、GNDLF の元の注文を DUMMY 顧客 ID に割り当てようとしますが、Customers テーブルには対応する行がありません。
DELETE FROM Customers
WHERE customerid = 'DUMMY'
UPDATE Customers
SET customerid = 'GLDRL'
WHERE customerid = 'GNDLF'
.Net SqlClient Data Provider: Msg 547, Level 16, State 0, Line 1(メッセージ 547、レベル 16、状態 0、行 1)
UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Orders_Customers'.
(UPDATE ステートメントは COLUMN FOREIGN KEY で、制約 'FK_Orders_Customers' と矛盾しています。)
The conflict occurred in database 'tempdb', table 'Customers', column 'customerid'.
(矛盾が発生したのは、データベース 'tempdb'、テーブル 'Customers'、列 'customerid' です。)
The statement has been terminated.
(ステートメントは終了されました。)
定義済みの参照動作を含む、外部キーの詳細については、sys.foreign_keys を参照してください。