適用対象:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Microsoft Fabric の SQL 分析エンドポイントMicrosoft Fabric のウェアハウスMicrosoft Fabric プレビューの SQL データベース
共通テーブル式 (CTE) は、それ自体を参照できるため、再帰 CTE を作成できるという大きな利点を提供します。 再帰 CTE は、完全な結果セットが取得されるまで、最初の CTE を繰り返し実行してデータのサブセットを返します。
クエリは、再帰 CTE を参照するときに再帰クエリと呼ばれます。 階層データの返しは、再帰クエリの一般的な用途です。 たとえば、組織図に従業員を表示したり、親製品に 1 つ以上のコンポーネントがあり、それらのコンポーネントが他の親のサブコンポーネントを持つ場合や、コンポーネントである場合がある部品表シナリオのデータを表示します。
再帰 CTE を使用すると、 SELECT、 INSERT、 UPDATE、 DELETE、または CREATE VIEW ステートメント内で再帰クエリを実行するために必要なコードを大幅に簡略化できます。 以前のバージョンの SQL Server では、再帰クエリでは通常、一時テーブル、カーソル、およびロジックを使用して再帰ステップのフローを制御する必要があります。 一般的なテーブル式の詳細については、「 WITH common_table_expression」を参照してください。
再帰 CTE の構造
Transact-SQL の再帰 CTE の構造は、他のプログラミング言語の再帰ルーチンに似ています。 他の言語の再帰ルーチンはスカラー値を返しますが、再帰 CTE は複数の行を返すことができます。
再帰 CTE は、次の 3 つの要素で構成されます。
ルーチンの呼び出し。
再帰 CTE の最初の呼び出しは、
UNION ALL、UNION、EXCEPT、またはINTERSECT演算子によって結合された 1 つ以上の CTE クエリ定義で構成されます。 これらのクエリ定義は CTE 構造体の基本結果セットを形成するため、アンカー メンバーと呼ばれます。CTE クエリ定義は、CTE 自体を参照しない限り、アンカー メンバーと見なされます。 すべてのアンカー メンバー クエリ定義は、最初の再帰メンバー定義の前に配置する必要があります。最後のアンカー メンバーを最初の再帰メンバーと結合するには、
UNION ALL演算子を使用する必要があります。ルーチンの再帰的な呼び出し。
再帰呼び出しには、CTE 自体を参照する
UNION ALL演算子によって結合された 1 つ以上の CTE クエリ定義が含まれます。 これらのクエリ定義は、再帰メンバーと呼ばれます。Termination check.
終了チェックは暗黙的です。再帰は、前の呼び出しから行が返されない場合に停止します。
Note
再帰 CTE が正しく構成されていないと、無限ループが発生する可能性があります。 たとえば、再帰メンバーのクエリ定義が親列と子列に対して同じ値を返す場合、無限ループが生成されます。 再帰クエリの結果をテストする場合は、MAXRECURSION、OPTION、INSERT、または UPDATE ステートメントの DELETE 句で、SELECT ヒントと 0 ~ 32,767 の値を使用して、特定のステートメントで許可される再帰レベルの数を制限できます。
詳細については、以下を参照してください。
擬似コードとセマンティクス
再帰 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 を繰り返します。
- 結果セットを返します。 これは、
UNION ALLするT0のTnです。
Examples
次の例は、 AdventureWorks2022 データベースの最上位の従業員から始まる従業員の階層リストを返すことによって、再帰 CTE 構造体のセマンティクスを示しています。 コード実行のチュートリアルは、この例に従います。
従業員テーブルを作成します。
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)
);
テーブルに値を設定します。
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
コードチュートリアルの例
再帰 CTE DirectReportsは、1 つのアンカー メンバーと 1 つの再帰メンバーを定義します。
アンカー メンバーは、基本結果セットの T0を返します。 これは、社内で最も高いランクの従業員です。 つまり、マネージャーに報告しない従業員です。
アンカー メンバーによって返される結果セットを次に示します。
ManagerID EmployeeID Title Level
--------- ---------- ----------------------------- ------
NULL 1 Chief Executive Officer 0
再帰メンバーは、アンカー メンバーの結果セット内の従業員の直属部下を返します。 これは、Employee テーブルと DirectReports CTE の間の結合操作によって実現されます。 再帰呼び出しを確立するのは、CTE 自体へのこの参照です。 入力 (DirectReports) として CTE Tiの従業員に基づいて、結合 (MyEmployees.ManagerID = DirectReports.EmployeeID) は出力 (Ti + 1) として返され、マネージャー (Ti) を持つ従業員が返されます。
したがって、再帰メンバーの最初の反復では、次の結果セットが返されます。
ManagerID EmployeeID Title Level
--------- ---------- ----------------------------- ------
1 273 Vice President of Sales 1
再帰メンバーは繰り返しアクティブ化されます。 再帰メンバーの 2 番目の反復では、ステップ 3 の単一行の結果セット (EmployeeIDの273を含む) を入力値として使用し、次の結果セットを返します。
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