共通テーブル式を使用する再帰クエリ
共通テーブル式 (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 社で地位が最も高い従業員から始まります。CTE を実行するステートメントにより、結果セットが研究開発グループの従業員に制限されます。コード実行のチュートリアルはこの例に沿っています。
USE AdventureWorks;
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 HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID 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 HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Research and Development' OR Level = 0;
GO
コード例のチュートリアル
DirectReports という再帰 CTE では、1 つのアンカー メンバと 1 つの再帰メンバを定義します。
アンカー メンバによって、基本結果セット T0 が返されます。これは、この会社で地位が最も高い従業員です。つまり、直属の上司がいない従業員です。
次に、アンカー メンバから返される結果セットを示します。
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ NULL 109 Chief Executive Officer 0再帰メンバにより、アンカー メンバの結果セットに含まれている従業員の直属の部下が返されます。この操作は、Employee テーブルと DirectReports CTE の結合操作で実現します。再帰呼び出しを確立するのは、この CTE 自体への参照です。結合 (Employee.ManagerID = DirectReports.EmployeeID) により、入力 (Ti) として CTE DirectReports の従業員に基づき、出力として (Ti+1) が返されます。この従業員の上司は (Ti) です。したがって、再帰メンバの最初の繰り返しにより、次の結果セットが返されます。
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ 109 12 Vice President of Engineering 1再帰メンバは、繰り返しアクティブになります。再帰メンバの 2 回目の繰り返しでは、(EmployeeID12 を含む) 手順 3. の 1 行の結果セットが入力値として使用され、次の結果セットが返されます。
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ 12 3 Engineering Manager 2再帰メンバの 3 回目の繰り返しでは、(EmployeeID3) を含む) 上記の 1 行の結果セットが入力値として使用され、次の結果セットが返されます。
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ 3 4 Senior Tool Designer 3 3 9 Design Engineer 3 3 11 Design Engineer 3 3 158 Research and Development Manager 3 3 263 Senior Tool Designer 3 3 267 Senior Design Engineer 3 3 270 Design Engineer 3再帰メンバの 4 回目の繰り返しでは、上記の EmployeeID の行セットの値 4、9、11、158、263、267、および 270 が入力値として使用されます。
この処理は、再帰メンバから空の結果セットが返されるまで繰り返されます。
実行中のクエリから返される最終的な結果セットは、アンカー メンバと再帰メンバによって生成されたすべての結果セットの和集合です。
次に上記の例から返される完全な結果セットを示します。
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ NULL 109 Chief Executive Officer 0 109 12 Vice President of Engineering 1 12 3 Engineering Manager 2 3 4 Senior Tool Designer 3 3 9 Design Engineer 3 3 11 Design Engineer 3 3 158 Research and Development Manager 3 3 263 Senior Tool Designer 3 3 267 Senior Design Engineer 3 3 270 Design Engineer 3 263 5 Tool Designer 4 263 265 Tool Designer 4 158 79 Research and Development Engineer 4 158 114 Research and Development Engineer 4 158 217 Research and Development Manager 4 (15 row(s) affected)