WITH common_table_expression (Transact-SQL)

  • [アーティクル]

共通テーブル式 (CTE) と呼ばれる一時的な名前付き結果セットを指定します。共通テーブル式は単純なクエリから派生し、単一の SELECT、INSERT、UPDATE、または DELETE ステートメントの実行スコープ内で定義されます。CTE は、CREATE VIEW ステートメントの中で、ビューの SELECT ステートメントの定義の一部として指定することもできます。共通テーブル式には、自己参照を含めることができます。これは再帰共通テーブル式と呼ばれます。

トピック リンク アイコンTransact-SQL 構文表記規則

構文

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
        expression_name [ ( column_name [ ,...n ] ) ]
    AS
        ( CTE_query_definition )

引数

  • expression_name
    共通テーブル式の有効な識別子です。 expression_name には、同一の WITH <common_table_expression> 句内で定義される他の共通テーブル式の名前と異なる名前を指定する必要があります。ただし、expression_name には、ベース テーブルまたはビューと同じ名前を指定できます。クエリ内で expression_name を参照する場合、ベース オブジェクトではなく、共通テーブル式が常に使用されます。
  • column_name
    共通テーブル式の列名を指定します。1 つの CTE 定義の中で、列名の重複は許可されません。指定する列名の数は、CTE_query_definition の結果セットの列数と一致する必要があります。クエリ定義内で、結果セットのすべての列に対して異なる列名が指定されている場合にのみ、列名リストをオプションで使用できます。

  • CTE_query_definition
    共通テーブル式を設定した結果セットを持つ SELECT ステートメントを指定します。CTE_query_definition の SELECT ステートメントは、ビューを作成する場合と同じ要件を満たす必要があります。ただし、ビューとは異なり、CTE で別の CTE を定義することはできません。詳細については、「CREATE VIEW (Transact-SQL)」の「解説」を参照してください。

    複数の CTE_query_definition を定義する場合は、UNION ALL、UNION、EXCEPT、INTERSECT のうちいずれかの set 演算子を使用してクエリ定義を連結する必要があります。再帰 CTE クエリ定義の使用方法の詳細については、以下の「解説」および「共通テーブル式を使用する再帰クエリ」を参照してください。

解説

CTE の作成および使用に関するガイドライン

非再帰 CTE には、次のガイドラインが適用されます。再帰 CTE に適用されるガイドラインについては、後述の「再帰 CTE の定義と使用に関するガイドライン」を参照してください。

  • CTE の後には、その CTE 列の一部または全部を参照する単一の SELECT、INSERT、UPDATE、または DELETE ステートメントを指定する必要があります。CTE は、CREATE VIEW ステートメントの中で、ビューの SELECT ステートメントの定義の一部として指定することもできます。
  • 非再帰 CTE では、複数の CTE クエリを定義できます。これらのクエリ定義は、UNION ALL、UNION、EXCEPT、または INTERSECT のいずれかの set 演算子で連結する必要があります。
  • CTE は、自分自身および同一の WITH 句内で先に定義された CTE を参照できます。前方参照は許可されません。
  • 1 つの CTE の中で複数の WITH 句を指定することはできません。たとえば、CTE_query_definition にサブクエリが含まれる場合、そのサブクエリに、別の CTE を定義している WITH 句を入れ子の状態で含めることはできません。
  • 次の句は、CTE_query_definition の中で使用できません。
    • COMPUTE または COMPUTE BY
    • ORDER BY (TOP 句が指定されている場合は除く)
    • INTO
    • クエリ ヒントを含む OPTION 句
    • FOR XML
    • FOR BROWSE
  • バッチの一部となるステートメント内で CTE が使用される場合、この句の前のステートメントの末尾にセミコロンを記述する必要があります。
  • CTE を参照するクエリは、カーソル定義に使用できます。
  • リモート サーバー上のテーブルは、CTE 内で参照できます。
  • CTE を実行するときには、クエリ内のビューを参照するヒントと同様に、CTE を参照するヒントと基になるテーブルに CTE がアクセスした際に発見されたその他のヒントとの間で、競合が発生する可能性があります。この競合が発生すると、クエリはエラーを返します。詳細については、「ビューの解決」を参照してください。

再帰 CTE の定義と使用に関するガイドライン

再帰 CTE の定義には、次のガイドラインが適用されます。

  • 再帰 CTE の定義には、少なくとも 2 つの CTE クエリ定義を含める必要があります。1 つはアンカー メンバで、もう 1 つは再帰メンバです。アンカー メンバと再帰メンバは複数定義できます。ただし、すべてのアンカー メンバの定義は、最初の再帰メンバの定義よりも前に記述する必要があります。CTE 自体を参照しない CTE クエリ定義はすべてアンカー メンバとなります。**
  • アンカー メンバは、UNION ALL、UNION、INTERSECT、または EXCEPT のいずれかの set 演算子で連結する必要があります。UNION ALL は、最後のアンカー メンバと最初の再帰メンバを連結する場合、および複数の再帰メンバを連結する場合に使用できる唯一の set 演算子です。
  • アンカー メンバの列数と再帰メンバの列数は、同じである必要があります。
  • 再帰メンバの列のデータ型は、アンカー メンバの対応する列のデータ型と同じである必要があります。
  • 再帰メンバの FROM 句は、CTE の expression_name を一度だけ参照します。
  • 再帰メンバの CTE_query_definition では、次のアイテムは許可されません。
    • SELECT DISTINCT
    • GROUP BY
    • HAVING
    • スカラ集計
    • TOP
    • LEFT、RIGHT、OUTER JOIN (INNER JOIN は使用できます)
    • サブクエリ
    • CTE_query_definition 内の、CTE に対する再帰参照に適用されるヒント

再帰 CTE の使用には、次のガイドラインが適用されます。

  • 再帰 CTE に含まれる SELECT ステートメントが返す列で NULL 値が許容されるかどうかにかかわらず、再帰 CTE が返すすべての列で NULL 値が許可されます。
  • 再帰 CTE が適切に構成されていない場合、無限ループが発生する可能性があります。たとえば、再帰メンバのクエリ定義が親列と子列に対して同じ値を返す場合、無限ループが生成されます。無限ループを防ぐには、MAXRECURSION ヒントを使用したり、INSERT、UPDATE、DELETE、または SELECT ステートメントの OPTION 句に 0 ～ 32,767 の値を指定したりすることにより、特定のステートメントに許可される再帰レベルの数を制限します。これにより、無限ループの原因となったコードの問題が解決されるまで、ステートメントの実行を制御できます。サーバー全体での既定値は 100 です。0 を指定した場合、制限は適用されません。MAXRECURSION の値は 1 つのステートメントに 1 つだけ指定できます。詳細については、「クエリ ヒント (Transact-SQL)」を参照してください。
  • 再帰共通テーブル式を含むビューを使用してデータを更新することはできません。
  • CTE を使用するクエリにカーソルを定義できます。CTE は カーソルの結果セットを定義する select_statement の引数です。再帰 CTE では、高速順方向専用および静的 (スナップショット) カーソルのみ使用できます。他の種類のカーソルを再帰 CTE で指定した場合、カーソルの種類は静的に変換されます。
  • リモート サーバー上のテーブルは、CTE 内で参照できます。CTE の再帰メンバがリモート サーバーを参照する場合、各リモート テーブルごとにスプールが作成されます。そのため、ローカルからそのテーブルに繰り返しアクセスできます。

例

A. 単純な共通テーブル式を作成する

次の例は、各マネージャに直接レポートする Adventure Works Cycles の従業員数を示しています。

USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) 
    FROM HumanResources.Employee AS e
    WHERE ManagerID IS NOT NULL
    GROUP BY ManagerID
)
SELECT ManagerID, DirectReports 
FROM DirReps 
ORDER BY ManagerID;
GO

B. 共通テーブル式を使用して、回数を制限し、平均数をレポートする

次の例は、マネージャにレポートする従業員の平均数を示しています。

WITH DirReps (Manager, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) AS DirectReports
    FROM HumanResources.Employee
    GROUP BY ManagerID
) 
SELECT AVG(DirectReports) AS [Average Number of Direct Reports]
FROM DirReps 
WHERE DirectReports>= 2 ;
GO

C. 共通テーブル式を複数回参照する

次の例は、SalesOrderHeader テーブルに記録された、各販売員ごとの販売注文数の合計と最新の販売注文日を示しています。ステートメントの実行中、CTE は 2 回参照されます。1 回は、選択された販売員の列を返すためであり、もう 1 回は、販売員のマネージャについて同様の詳細を取得するためです。販売員とマネージャのデータは 1 行で返されます。

USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_CTE AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO

D. 再帰共通テーブル式を使用して、複数の再帰レベルを表示する

次の例は、マネージャおよびマネージャにレポートする従業員の階層リストを示しています。

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports ;
GO

E. 再帰共通テーブル式を使用して、2 つの再帰レベルを表示する

次の例は、マネージャおよびマネージャにレポートする従業員を示しています。返されるレベルの数は 2 つに制限されます。

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports 
WHERE EmployeeLevel <= 2 ;
GO

F. 再帰共通テーブル式を使用して、階層リストを表示する

次の例は、例 C にマネージャと従業員の名前および各自の役職を追加したものです。各レベルをインデントすることにより、マネージャおよび従業員の階層をさらに強調しています。

USE AdventureWorks;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(varchar(255), c.FirstName + ' ' + c.LastName)
    FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
    FROM HumanResources.Employee as e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports 
ORDER BY Sort;
GO

G. MAXRECURSION を使用して、ステートメントをキャンセルする

MAXRECURSION を使用すると、不適切に作成された再帰 CTE による無限ループの発生を防ぐことができます。次の例では、無限ループを意図的に作成し、MAXRECURSION ヒントを使用して再帰レベルの数を 2 に制限しています。

USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee AS e 
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

コードのエラーが訂正されると、MAXRECURSION は不要になります。次の例は、訂正されたコードを示しています。

USE AdventureWorks;
GO
WITH cte (EmployeeID, ManagerID, Title)
AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT  e.EmployeeID, e.ManagerID, e.Title
    FROM HumanResources.Employee AS e
    JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;
GO

H. 共通テーブル式を使用して、SELECT ステートメント内の再帰リレーションシップを選択的にステップ スルーする

次の例は、ProductAssemblyID = 800 の自転車を組み立てるのに必要な製品アセンブリとコンポーネントの階層を示しています。

USE AdventureWorks;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
        b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom 
        INNER JOIN Parts AS p
        ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
        ComponentLevel 
FROM Parts AS p
    INNER JOIN Production.Product AS pr
    ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;
GO

I. UPDATE ステートメントで再帰 CTE を使用する

次の例は、ManagerID 12 に直接または間接的にレポートしたすべての従業員の 25% に対して VacationHours の値を更新します。共通テーブル式は、ManagerID 12 に直接レポートした従業員やその従業員にレポートした従業員などを含む、従業員の階層リストを返します。共通テーブル式が返した行のみが変更されます。

USE AdventureWorks;
GO
WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS
(SELECT e.EmployeeID, e.VacationHours, 1
  FROM HumanResources.Employee AS e
  WHERE e.ManagerID = 12
  UNION ALL
  SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
  FROM HumanResources.Employee as e
  JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;
GO

J. 複数のアンカー メンバと再帰メンバを使用する

次の例では、複数のアンカー メンバと再帰メンバを使用して、指定された個人のすべての先祖を返します。テーブルが 1 つ作成され、値が挿入されます。このテーブルは、再帰 CTE が返す家系図になります。

-- Genealogy table
IF OBJECT_ID('Person','U') IS NOT NULL DROP TABLE Person;
GO
CREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT Person VALUES(1, 'Sue', NULL, NULL);
INSERT Person VALUES(2, 'Ed', NULL, NULL);
INSERT Person VALUES(3, 'Emma', 1, 2);
INSERT Person VALUES(4, 'Jack', 1, 2);
INSERT Person VALUES(5, 'Jane', NULL, NULL);
INSERT Person VALUES(6, 'Bonnie', 5, 4);
INSERT Person VALUES(7, 'Bill', 5, 4);
GO
-- Create the recursive CTE to find all of Bonnie's ancestors.
WITH Generation (ID) AS
(
-- First anchor member returns Bonnie's mother.
    SELECT Mother 
    FROM Person
    WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
    SELECT Father 
    FROM Person
    WHERE Name = 'Bonnie'
UNION ALL
-- First recursive member returns male ancestors of the previous generation.
    SELECT Person.Father
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
UNION ALL
-- Second recursive member returns female ancestors of the previous generation.
    SELECT Person.Mother
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, Person
WHERE Generation.ID = Person.ID;
GO

参照

関連項目

CREATE VIEW (Transact-SQL)
DELETE (Transact-SQL)
EXCEPT および INTERSECT (Transact-SQL)
INSERT (Transact-SQL)
SELECT (Transact-SQL)
UPDATE (Transact-SQL)

その他の技術情報

共通テーブル式を使用する再帰クエリ
共通テーブル式の使用

ヘルプおよび情報

SQL Server 2005 の参考資料の入手