共通テーブル式を使用する再帰クエリ
共通テーブル式 (CTE) には、自身を参照して再帰 CTE を作成できるという大きな利点があります。再帰 CTE は、完全な結果セットを取得できるまで、最初の CTE を繰り返し実行してデータのサブセットを返す CTE です。
再帰 CTE を参照するときのクエリを再帰クエリと呼びます。再帰クエリの一般的な使用方法として、階層データを返す処理があります。たとえば、組織図に含まれている従業員を表示する処理や、親製品に 1 つ以上のコンポーネントがある部品表で、個々のコンポーネントにサブコンポーネントがあったり、コンポーネントが他の親のコンポーネントであるような場合に、データを表示する処理に使用できます。
再帰 CTE を使用すると、SELECT、INSERT、UPDATE、DELETE、または CREATE VIEW の各ステートメント内で再帰クエリを実行するために必要なコードを大幅に簡素化できます。以前のバージョンの SQL Server では、再帰クエリには通常、一時テーブル、カーソル、および再帰手順の流れを制御するロジックを使用する必要がありました。共通テーブル式の詳細については、「共通テーブル式の使用」を参照してください。
再帰 CTE の構造
Transact-SQL の再帰 CTE の構造は、他のプログラミング言語の再帰ルーチンと似ています。他の言語の再帰ルーチンからはスカラー値が返されますが、再帰 CTE からは複数の行が返されます。
再帰 CTE は、次の 3 つの要素で構成されます。
ルーチンの呼び出し。
再帰 CTE の最初の呼び出しは、UNION ALL、UNION、EXCEPT、または INTERSECT のいずれかの演算子で結合された 1 つ以上の CTE_query_definitions で構成されます。これらのクエリ定義により CTE 構造の基本結果セットが作成されるので、このようなクエリ定義はアンカー メンバーと呼ばれます。
CTE_query_definitions は、CTE 自体を参照する場合を除いて、アンカー メンバーと見なされます。アンカー メンバーのすべてのクエリ定義は、最初の再帰メンバー定義の前に位置付ける必要があり、UNION ALL 演算子を使用して、最後のアンカー メンバーと最初の再帰メンバーを結合する必要があります。
ルーチンの再帰呼び出し。
再帰呼び出しには、UNION ALL 演算子で結合された、CTE 自体を参照する 1 つ以上の CTE_query_definitions が含まれます。このようなクエリ定義は、再帰メンバーと呼ばれます。
終了チェック。
終了チェックは暗黙的な処理です。前のルーチンの呼び出しから行が返されなかった場合、再帰処理が停止します。
.gif "注意") 注 |
|---|
不適切に作成された再帰 CTE は無限ループの原因となる可能性があります。たとえば、再帰メンバー クエリ定義によって親列と子列に同じ値が返される場合、無限ループが作成されます。再帰クエリの結果をテストする際には、INSERT、UPDATE、DELETE、または SELECT のいずれかのステートメントの OPTION 句で MAXRECURSION ヒントおよび 0 から 32,767 までの値を使用して、特定のステートメントで使用できる再帰レベルの数を制限できます。詳細については、「クエリ ヒント (Transact-SQL)」および「WITH common_table_expression (Transact-SQL)」を参照してください。 |
擬似コードとセマンティクス
再帰 CTE の構造には、少なくとも 1 つのアンカー メンバーと 1 つの再帰メンバーが含まれている必要があります。次の擬似コードは、1 つのアンカー メンバーと 1 つの再帰メンバーを含む単純な再帰 CTE のコンポーネントを示しています。
WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name
この再帰式のセマンティクスは次のとおりです。
CTE 式をアンカー メンバーと再帰メンバーに分割します。
最初の呼び出しまたは基本結果セット (T0) を作成するアンカー メンバーを実行します。
入力として Ti を、出力として Ti+1 を指定して、再帰メンバーを実行します。
空のセットが返されるまで、手順 3. を繰り返します。
結果セットを返します。この結果セットは、T0 から Tn までを UNION ALL で結合したものです。
例
次の例は、従業員の階層一覧を返すことで、再帰 CTE 構造のセマンティクスを示しています。この一覧は、Adventure Works Cycles 社で地位が最も高い従業員から始まります。コード実行のチュートリアルはこの例に沿っています。
-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
EmployeeID smallint NOT NULL,
FirstName nvarchar(30) NOT NULL,
LastName nvarchar(40) NOT NULL,
Title nvarchar(50) NOT NULL,
DeptID smallint NOT NULL,
ManagerID int NULL,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES
(1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16, N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);
USE AdventureWorks2008R2;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
0 AS Level
FROM dbo.MyEmployees AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
Level + 1
FROM dbo.MyEmployees AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, DeptID, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Sales and Marketing' OR Level = 0;
GO
コード例のチュートリアル
DirectReports という再帰 CTE では、1 つのアンカー メンバーと 1 つの再帰メンバーを定義します。
アンカー メンバーによって、基本結果セット T0 が返されます。これは、この会社で地位が最も高い従業員です。つまり、直属の上司がいない従業員です。
次に、アンカー メンバーから返される結果セットを示します。
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ NULL 1 Chief Executive Officer 0再帰メンバーにより、アンカー メンバーの結果セットに含まれている従業員の直属の部下が返されます。この操作は、Employee テーブルと DirectReports CTE の結合操作で実現します。再帰呼び出しを確立するのは、この CTE 自体への参照です。結合 (MyEmployees.ManagerID = DirectReports.EmployeeID) により、入力 (Ti) として CTE DirectReports の従業員に基づき、出力として (Ti+1) が返されます。この従業員の上司は (Ti) です。したがって、再帰メンバーの最初の繰り返しにより、次の結果セットが返されます。
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ 1 273 Vice President of Sales 1再帰メンバーは、繰り返しアクティブになります。再帰メンバーの 2 回目の繰り返しでは、(EmployeeID273 を含む) 手順 3. の 1 行の結果セットが入力値として使用され、次の結果セットが返されます。
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ 273 16 Marketing Manager 2 273 274 North American Sales Manager 2 273 285 Pacific Sales Manager 2再帰メンバーの 3 回目の繰り返しでは、上記の結果セットが入力値として使用され、次の結果セットが返されます。
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ 16 23 Marketing Specialist 3 274 275 Sales Representative 3 274 276 Sales Representative 3 285 286 Sales Representative 3実行中のクエリから返される最終的な結果セットは、アンカー メンバーと再帰メンバーによって生成されたすべての結果セットの和集合です。
次に上記の例から返される完全な結果セットを示します。
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ NULL 1 Chief Executive Officer 0 1 273 Vice President of Sales 1 273 16 Marketing Manager 2 273 274 North American Sales Manager 2 273 285 Pacific Sales Manager 2 16 23 Marketing Specialist 3 274 275 Sales Representative 3 274 276 Sales Representative 3 285 286 Sales Representative 3