[アーティクル]
04/01/2012

WITH common_table_expression (Transact-SQL)

共通テーブル式 (CTE) と呼ばれる一時的な名前付き結果セットを指定します。共通テーブル式は単純なクエリから派生し、単一の SELECT、INSERT、UPDATE、MERGE、または 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 列の一部または全部を参照する単一の SELECT、INSERT、UPDATE、MERGE、または 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 が UPDATE ステートメントの対象である場合、ステートメント内の CTE に対するすべての参照を一致させる必要があります。たとえば、FROM 句で CTE に別名を割り当てた場合、CTE に対するすべての参照で別名を使用する必要があります。CTE へのあいまいな参照は、予期しない結合動作やクエリ結果につながる場合があります。詳細については、「UPDATE (Transact-SQL)」を参照してください。

再帰共通テーブル式の定義および使用に関するガイドライン

再帰共通テーブル式の定義には、次のガイドラインが適用されます。

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

例

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

次の例は、Adventure Works Cycles に記録された、販売員ごとの年間の販売注文数の合計を示しています。

USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO

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

次の例は、販売員のすべての年の平均販売注文数を示しています。

WITH Sales_CTE (SalesPersonID, NumberOfOrders)
AS
(
    SELECT SalesPersonID, COUNT(*)
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID
)
SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"
FROM Sales_CTE;
GO

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

次の例は、マネージャーおよびマネージャーにレポートする従業員の階層リストを示しています。まず、dbo.MyEmployees テーブルを作成してデータを設定します。

-- 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, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM dbo.MyEmployees 
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.MyEmployees AS e
        INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel 
FROM DirectReports
ORDER BY ManagerID;
GO

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

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

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

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

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

USE AdventureWorks2008R2;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), e.FirstName + ' ' + e.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(varchar(255), e.FirstName + ' ' + e.LastName)
    FROM dbo.MyEmployees AS e
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT CONVERT(varchar(255), REPLICATE ('|    ' , EmployeeLevel) +
        e.FirstName + ' ' + e.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (varchar(255), RTRIM(Sort) + '|    ' + FirstName + ' ' + 
                 LastName)
    FROM dbo.MyEmployees AS e
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports 
ORDER BY Sort;
GO

F. MAXRECURSION を使用して、ステートメントを取り消す

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

USE AdventureWorks2008R2;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM dbo.MyEmployees
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  dbo.MyEmployees 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 AdventureWorks2008R2;
GO
WITH cte (EmployeeID, ManagerID, Title)
AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM dbo.MyEmployees
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT  e.EmployeeID, e.ManagerID, e.Title
    FROM dbo.MyEmployees AS e
    JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;
GO

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

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

USE AdventureWorks2008R2;
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

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

次の例では、Road-550-W Yellow、44 という製品 (ProductAssemblyID800) の製造に使用されるすべての部品の PerAssemblyQty の値を更新します。共通テーブル式は、ProductAssemblyID 800 の製造に直接使用される部品やその部品の製造に使用されるコンポーネントなどを含む、部品の階層リストを返します。共通テーブル式が返した行のみが変更されます。

USE AdventureWorks2008R2;
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
)
UPDATE Production.BillOfMaterials
SET PerAssemblyQty = c.PerAssemblyQty * 2
FROM Production.BillOfMaterials AS c
JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID
WHERE d.ComponentLevel = 0;

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

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

-- Genealogy table
IF OBJECT_ID('dbo.Person','U') IS NOT NULL DROP TABLE dbo.Person;
GO
CREATE TABLE dbo.Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT dbo.Person 
VALUES(1, 'Sue', NULL, NULL)
      ,(2, 'Ed', NULL, NULL)
      ,(3, 'Emma', 1, 2)
      ,(4, 'Jack', 1, 2)
      ,(5, 'Jane', NULL, NULL)
      ,(6, 'Bonnie', 5, 4)
      ,(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 dbo.Person
    WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
    SELECT Father 
    FROM dbo.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, dbo.Person
    WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, dbo.Person
WHERE Generation.ID = Person.ID;
GO

J. 再帰 CTE で分析関数を使用する

次の例は、CTE の再帰部分で分析関数または集計関数を使用するときに生じる可能性がある落とし穴を示しています。

DECLARE @t1 TABLE (itmID int, itmIDComp int);
INSERT @t1 VALUES (1,10), (2,10); 

DECLARE @t2 TABLE (itmID int, itmIDComp int); 
INSERT @t2 VALUES (3,10), (4,10); 

WITH vw AS
 (
    SELECT itmIDComp, itmID
    FROM @t1

    UNION ALL

    SELECT itmIDComp, itmID
    FROM @t2
) 
,r AS
 (
    SELECT t.itmID AS itmIDComp
           , NULL AS itmID
           ,CAST(0 AS bigint) AS N
           ,1 AS Lvl
    FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t (itmID) 

UNION ALL

SELECT t.itmIDComp
    , t.itmID
    , ROW_NUMBER() OVER(PARTITION BY t.itmIDComp ORDER BY t.itmIDComp, t.itmID) AS N
    , Lvl + 1
FROM r 
    JOIN vw AS t ON t.itmID = r.itmIDComp
) ;

SELECT Lvl, N FROM r

次の結果は、クエリの予想結果です。

次の結果は、クエリの実際の結果です。

N は、CTE の再帰部分を通過するたびに 1 を返します。これは、その再帰レベルのデータのサブセットのみが ROWNUMBER に渡されるからです。クエリの再帰部分を反復するたびに、1 つの行のみが ROWNUMBER に渡されます。

WITH common_table_expression (Transact-SQL)

構文

引数

説明

共通テーブル式の作成および使用に関するガイドライン

再帰共通テーブル式の定義および使用に関するガイドライン

例

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

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

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

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

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

F. MAXRECURSION を使用して、ステートメントを取り消す

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

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

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

J. 再帰 CTE で分析関数を使用する

関連項目

参照

概念

その他のリソース

WITH common_table_expression (Transact-SQL)

構文

引数

説明

共通テーブル式の作成および使用に関するガイドライン

再帰共通テーブル式の定義および使用に関するガイドライン

例

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

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

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

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

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

F. MAXRECURSION を使用して、ステートメントを取り消す

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

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

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

J. 再帰 CTE で分析関数を使用する

関連項目

参照

概念

その他のリソース

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

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