共通テーブル式を使用した再帰クエリ

9629 ワード
再帰的に照会する
http://msdn.microsoft.com/zh-cn/library/ms186243.aspx
 
共通テーブル式を使用した再帰クエリ
SQL Server 2008 R2
 
その他のバージョン

 
0(合計1)本明細書の評価に役立つ−このテーマを評価
 
 
共通テーブル式(CTE)は、それ自体を参照して再帰CTEを作成できるという重要な利点を有する.再帰CTEは、完全な結果セットを取得するまで、初期CTEを繰り返し実行してデータのサブセットを返す共通テーブル式である.
クエリが再帰CTEを参照する場合、それは再帰クエリと呼ばれます.再帰クエリは、通常、階層化されたデータを返すために使用されます.たとえば、親製品に1つ以上のコンポーネントがあり、そのコンポーネントにサブコンポーネント、または他の親製品のコンポーネントがある場合がある組織図の従業員またはBOMスキーマのデータを表示します.
再帰CTEは、SELECT、INSERT、UPDATE、DELETE、またはCREATE VIEW文で再帰クエリを実行するために必要なコードを大幅に簡略化することができる.SQL Serverの以前のバージョンでは、再帰クエリは通常、一時テーブル、カーソル、論理を使用して再帰ステップフローを制御する必要があります.共通テーブル式の詳細については、共通テーブル式の使用を参照してください.
再帰CTEの構造
Transact−SQLにおける再帰CTEの構造は他のプログラミング言語における再帰ルーチンと類似している.他の言語の再帰ルーチンはスカラー値を返すが、再帰CTEは複数行を返すことができる.
再帰CTEは、次の3つの要素から構成されています.
  • ルーチンの呼び出し.再帰CTEの第1の呼び出しは、ユニオンALL、ユニオン、EXCEPTまたはINTERSECT演算子によって結合された1つまたは複数のCTE＿を含むquery_definitions.これらのクエリ定義はCTE構造のベンチマーク結果セットを形成するため、「ポイントメンバーの位置決め」と呼ばれます.CTE_query_definitionsは、CTE自体を参照しない限り、位置決めポイントメンバーと見なされます.すべてのロケータメンバークエリー定義は、最初の再帰メンバー定義の前に配置する必要があります.また、UNIOALL演算子を使用して最後のロケータメンバーと最初の再帰メンバーを結合する必要があります.
  • ルーチンの再帰呼び出し.再帰的呼び出しは、CTE自体を参照するUNIOALL演算子によって結合された1つまたは複数のCTE＿を含むquery_definitions.これらのクエリー定義は「再帰メンバー」と呼ばれます.
  • 検査を終了します.チェックの終了は暗黙的です.前の呼び出しでローが返されなかった場合、再帰は停止します.

    • 注意注意
    再帰CTEの組み合わせが正しくない場合、無限ループを引き起こす可能性があります.たとえば、再帰メンバー・クエリー定義が親カラムと子カラムに同じ値を返す場合、無限ループが発生します.再帰クエリの結果をテストするときは、INSERT、UPDATE、DELETEまたはSELECT文のOPTION句でMAXRECURSIONプロンプトと0～32767の値を使用することで、特定の文で許可される再帰級数を制限できます.詳細については、クエリーヒント(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式は、位置決めポイントメンバーと再帰メンバーに分割されます.
  • 位置決めポイントメンバーを実行し、最初の呼び出しまたは基準結果セット(T 0)を作成します.
  • 再帰メンバーを実行し、Tiを入力とし、Ti+1を出力とする.
  • は、空のセットに戻るまで手順3を繰り返します.
  • は結果セットを返します.これは、T 0~Tnに対してUNIOALLを実行した結果である.

    • 例
    次の例では、Adventure Works Cycles社の従業員の階層リスト(最上位従業員から)を返すことで、再帰CTE構造の意味を示します.例の後にコード実行の演習があります.
    Transact-SQL
      
    -- 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);

    Transact-SQL
      
    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は、位置決めポイントメンバーと再帰メンバーを定義します.
  • 位置決めポイントメンバーは、基準結果セットT 0を返す.これが会社の最上位の従業員です.つまり、マネージャーに報告しない従業員です.次は、アンカーポイントメンバーが返す結果セットです.
     
      
    ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
NULL      1          Chief Executive Officer        0

  • 再帰メンバーは、ロケーションポイントメンバー結果セットの従業員の直接部下を返します.これは、EmployeeテーブルとDirectReports CTEとの間で結合動作を実行することによって得られる.今回,CTE自身の参照に対して再帰呼び出しが確立された.CTE DirectReportsの従業員を入力(Ti)として利用し、結合(MyEmployees.ManagerID=DirectReports.EmployeeID)を出力(Ti)としてマネージャーの従業員に返す.これにより、再帰メンバーの最初の反復は、次の結果セットを返します.
     
      
    ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
1         273        Vice President of Sales       1

  • 再帰メンバーの再アクティブ化を繰り返します.再帰メンバーの2回目の反復は、ステップ3の1行結果セット(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


    • 参照
    リファレンス
    WITH common_table_expression (Transact-SQL)
    クエリーヒント(Transact-SQL)
    INSERT (Transact-SQL)
    UPDATE (Transact-SQL)
    DELETE (Transact-SQL)
    EXCEPTとINTERSECT(Transact-SQL)
    XenからKVMへのゲストイメージ変換
    【2018 BSUIR Final C】Partial Sums問題解