WITH common_table_expression (Transact-SQL)WITH common_table_expression (Transact-SQL)

適用対象: ○SQL Server (2008 以降) ○Azure SQL Database ○Azure SQL Data Warehouse ○Parallel Data Warehouse APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

共通テーブル式 (CTE) と呼ばれる一時的な名前付き結果セットを指定します。Specifies a temporary named result set, known as a common table expression (CTE). 共通テーブル式は単純なクエリから派生し、単一の SELECT、INSERT、UPDATE、または DELETE ステートメントの実行スコープ内で定義されます。This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. CTE は、CREATE VIEW ステートメントの中で、ビューの SELECT ステートメントの定義の一部として指定することもできます。This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. 共通テーブル式には、自己参照を含めることができます。A common table expression can include references to itself. これは再帰共通テーブル式と呼ばれます。This is referred to as a recursive common table expression.

トピック リンク アイコン Transact-SQL 構文表記規則Topic link icon Transact-SQL Syntax Conventions

構文Syntax

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

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

引数Arguments

expression_nameexpression_name
共通テーブル式の有効な識別子です。Is a valid identifier for the common table expression. expression_name には、同一の WITH <common_table_expression> 句内で定義される他の共通テーブル式の名前と異なる名前を指定する必要があります。ただし、expression_name には、ベース テーブルまたはビューと同じ名前を指定できます。expression_name must be different from the name of any other common table expression defined in the same WITH <common_table_expression> clause, but expression_name can be the same as the name of a base table or view. クエリの expression_name の参照では、ベース オブジェクトではなく、共通テーブル式が使用されます。Any reference to expression_name in the query uses the common table expression and not the base object.

column_namecolumn_name
共通テーブル式の列名を指定します。Specifies a column name in the common table expression. 1 つの CTE 定義の中で、列名の重複は許可されません。Duplicate names within a single CTE definition are not allowed. 指定した列名の数は CTE_query_definition の結果セットの列数と一致する必要があります。The number of column names specified must match the number of columns in the result set of the CTE_query_definition. クエリ定義内で、結果セットのすべての列に対して異なる列名が指定されている場合にのみ、列名リストをオプションで使用できます。The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

CTE_query_definitionCTE_query_definition
共通テーブル式を設定した結果セットを持つ SELECT ステートメントを指定します。Specifies a SELECT statement whose result set populates the common table expression. CTE_query_definition の SELECT ステートメントでは、CTE は別の CTE を定義できないという点を除き、ビューの作成と同じ要件を満たす必要があります。The SELECT statement for CTE_query_definition must meet the same requirements as for creating a view, except a CTE cannot define another CTE. 詳細については、「解説」セクションと「CREATE VIEW (Transact-SQL)」を参照してください。For more information, see the Remarks section and CREATE VIEW (Transact-SQL).

複数の CTE_query_definition が定義されている場合、set 演算子 UNION ALL、UNION、EXCEPT、INTERSECT のいずれかでクエリ定義を結合する必要があります。If more than one CTE_query_definition is defined, the query definitions must be joined by one of these set operators: UNION ALL, UNION, EXCEPT, or INTERSECT.

RemarksRemarks

共通テーブル式の作成および使用に関するガイドラインGuidelines for Creating and Using Common Table Expressions

非再帰共通テーブル式には、次のガイドラインが適用されます。The following guidelines apply to nonrecursive common table expressions. 再帰共通テーブル式に適用されるガイドラインについては、後述の「再帰共通テーブル式の定義および使用に関するガイドライン」を参照してください。For guidelines that apply to recursive common table expressions, see "Guidelines for Defining and Using Recursive Common Table Expressions" that follows.

  • CTE の後には、その CTE 列の一部または全部を参照する単一の SELECT、INSERT、UPDATE、または DELETE ステートメントを指定する必要があります。A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns. CTE は、ビューの SELECT ステートメントの定義の一部として CREATE VIEW ステートメントに指定することもできます。A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.

  • 非再帰 CTE では、複数の CTE クエリを定義できます。Multiple CTE query definitions can be defined in a nonrecursive CTE. 定義は、set 演算子 UNION ALL、UNION、INTERSECT、または EXCEPT のいずれかによって結合する必要があります。The definitions must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT.

  • CTE は、自分自身および同一の WITH 句内で先に定義された CTE を参照できます。A CTE can reference itself and previously defined CTEs in the same WITH clause. 前方参照は許可されません。Forward referencing is not allowed.

  • 1 つの CTE の中で複数の WITH 句を指定することはできません。Specifying more than one WITH clause in a CTE is not allowed. たとえば、CTE_query_definition にサブクエリが含まれる場合、そのサブクエリには、別の CTE を定義する入れ子の WITH 句を含めることができません。For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

  • 次の句は CTE_query_definition で使用できません。The following clauses cannot be used in the CTE_query_definition:

    • ORDER BY (TOP 句が指定されている場合は除く)ORDER BY (except when a TOP clause is specified)

    • INTOINTO

    • クエリ ヒントを含む OPTION 句OPTION clause with query hints

    • FOR BROWSEFOR BROWSE

  • バッチの一部となるステートメント内で CTE が使用される場合、この句の前のステートメントの末尾にセミコロンを記述する必要があります。When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

  • CTE を参照するクエリは、カーソル定義に使用できます。A query referencing a CTE can be used to define a cursor.

  • リモート サーバー上のテーブルは、CTE 内で参照できます。Tables on remote servers can be referenced in the CTE.

  • CTE を実行するときには、クエリ内のビューを参照するヒントと同様に、CTE を参照するヒントと基になるテーブルに CTE がアクセスした際に発見されたその他のヒントとの間で、競合が発生する可能性があります。When executing a CTE, any hints that reference a CTE may conflict with other hints that are discovered when the CTE accesses its underlying tables, in the same manner as hints that reference views in queries. この競合が発生すると、クエリはエラーを返します。When this occurs, the query returns an error.

再帰共通テーブル式の定義および使用に関するガイドラインGuidelines for Defining and Using Recursive Common Table Expressions

再帰共通テーブル式の定義には、次のガイドラインが適用されます。The following guidelines apply to defining a recursive common table expression:

  • 再帰 CTE の定義には、少なくとも 2 つの CTE クエリ定義を含める必要があります。1 つはアンカー メンバーで、もう 1 つは再帰メンバーです。The recursive CTE definition must contain at least two CTE query definitions, an anchor member and a recursive member. アンカー メンバーと再帰メンバーは複数定義できます。ただし、すべてのアンカー メンバーの定義は、最初の再帰メンバーの定義よりも前に記述する必要があります。Multiple anchor members and recursive members can be defined; however, all anchor member query definitions must be put before the first recursive member definition. CTE 自体を参照しない CTE クエリ定義はすべてアンカー メンバーとなります。All CTE query definitions are anchor members unless they reference the CTE itself.

  • アンカー メンバーは、set 演算子 UNION ALL、UNION、INTERSECT、または EXCEPT のいずれかによって結合する必要があります。Anchor members must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT. UNION ALL は、最後のアンカー メンバーと最初の再帰メンバーを連結する場合、および複数の再帰メンバーを連結する場合に使用できる唯一の set 演算子です。UNION ALL is the only set operator allowed between the last anchor member and first recursive member, and when combining multiple recursive members.

  • アンカー メンバーの列数と再帰メンバーの列数は、同じである必要があります。The number of columns in the anchor and recursive members must be the same.

  • 再帰メンバーの列のデータ型は、アンカー メンバーの対応する列のデータ型と同じである必要があります。The data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member.

  • 再帰メンバーの FROM 句は、CTE の expression_name を一度だけ参照する必要があります。The FROM clause of a recursive member must refer only one time to the CTE expression_name.

  • 次の項目は再帰メンバーの CTE_query_definition で許可されません。The following items are not allowed in the CTE_query_definition of a recursive member:

    再帰共通テーブル式の使用には、次のガイドラインが適用されます。The following guidelines apply to using a recursive common table expression:

  • 再帰 CTE に含まれる SELECT ステートメントが返す列で NULL 値が許容されるかどうかにかかわらず、再帰 CTE が返すすべての列で NULL 値が許可されます。All columns returned by the recursive CTE are nullable regardless of the nullability of the columns returned by the participating SELECT statements.

  • 再帰 CTE が適切に構成されていない場合、無限ループが発生する可能性があります。An incorrectly composed recursive CTE may cause an infinite loop. たとえば、再帰メンバーのクエリ定義が親列と子列に対して同じ値を返す場合、無限ループが生成されます。For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. 無限ループを防ぐには、MAXRECURSION ヒントを使用したり、INSERT、UPDATE、DELETE、または SELECT ステートメントの OPTION 句に 0 ~ 32,767 の値を指定したりすることにより、特定のステートメントに許可される再帰レベルの数を制限します。To prevent an infinite loop, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement. これにより、無限ループの原因となったコードの問題が解決されるまで、ステートメントの実行を制御できます。This lets you control the execution of the statement until you resolve the code problem that is creating the loop. サーバー全体での既定値は 100 です。The server-wide default is 100. 0 を指定した場合、制限は適用されません。When 0 is specified, no limit is applied. MAXRECURSION の値は 1 つのステートメントに 1 つだけ指定できます。Only one MAXRECURSION value can be specified per statement. 詳細については、「クエリ ヒント (Transact-SQL)」を参照してください。For more information, see Query Hints (Transact-SQL).

  • 再帰共通テーブル式を含むビューを使用してデータを更新することはできません。A view that contains a recursive common table expression cannot be used to update data.

  • CTE を使用するクエリにカーソルを定義できます。Cursors may be defined on queries using CTEs. CTE は、カーソルの結果セットを定義する select_statement 引数です。The CTE is the select_statement argument that defines the result set of the cursor. 再帰 CTE では、高速順方向専用および静的 (スナップショット) カーソルのみ使用できます。Only fast forward-only and static (snapshot) cursors are allowed for recursive CTEs. 他の種類のカーソルを再帰 CTE で指定した場合、カーソルの種類は静的に変換されます。If another cursor type is specified in a recursive CTE, the cursor type is converted to static.

  • リモート サーバー上のテーブルは、CTE 内で参照できます。Tables on remote servers may be referenced in the CTE. CTE の再帰メンバーがリモート サーバーを参照する場合、各リモート テーブルごとにスプールが作成されます。そのため、ローカルからそのテーブルに繰り返しアクセスできます。If the remote server is referenced in the recursive member of the CTE, a spool is created for each remote table so the tables can be repeatedly accessed locally. CTE クエリの場合、クエリ プランに Index Spool/Lazy Spool が表示され、WITH STACK 述語が付加されます。If it is a CTE query, Index Spool/Lazy Spools is displayed in the query plan and will have the additional WITH STACK predicate. これは、適切な再帰を確認する方法の 1 つです。This is one way to confirm proper recursion.

  • CTE の再帰部分の分析関数と集計関数は、CTE のセットではなく、現在の再帰レベルのセットに適用されます。Analytic and aggregate functions in the recursive part of the CTE are applied to the set for the current recursion level and not to the set for the CTE. ROW_NUMBER などの関数は、現在の再帰レベルによって渡されたデータのサブセットでのみ機能し、CTE の再帰部分に渡されたデータのセット全体では機能しません。Functions like ROW_NUMBER operate only on the subset of data passed to them by the current recursion level and not the entire set of data pased to the recursive part of the CTE. 詳細については、例 k. を使用してで分析関数に続く CTE の再帰的なを参照してください。For more information, see example K. Using analytical functions in a recursive CTE that follows.

SQL データ ウェアハウスSQL Data WarehouseParallel Data WarehouseParallel Data Warehouse の共通テーブル式の機能と制限Features and Limitations of Common Table Expressions in SQL データ ウェアハウスSQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse

SQL データ ウェアハウスSQL Data WarehouseParallel Data WarehouseParallel Data Warehouse の CTE の現在の実装には、次のような機能と制限があります。The current implementation of CTEs in SQL データ ウェアハウスSQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse have the following features and limitations:

  • CTE は SELECT ステートメントに指定できます。A CTE can be specified in a SELECT statement.

  • CTE は CREATE VIEW ステートメントに指定できます。A CTE can be specified in a CREATE VIEW statement.

  • CTE は CREATE TABLE AS SELECT (CTAS) ステートメントに指定できます。A CTE can be specified in a CREATE TABLE AS SELECT (CTAS) statement.

  • CTE は CREATE REMOTE TABLE AS SELECT (CRTAS) ステートメントに指定できます。A CTE can be specified in a CREATE REMOTE TABLE AS SELECT (CRTAS) statement.

  • CTE は CREATE EXTERNAL TABLE AS SELECT (CETAS) ステートメントに指定できます。A CTE can be specified in a CREATE EXTERNAL TABLE AS SELECT (CETAS) statement.

  • リモート テーブルは CTE から参照できます。A remote table can be referenced from a CTE.

  • 外部テーブルは CTE から参照できます。An external table can be referenced from a CTE.

  • CTE では、複数の CTE クエリを定義できます。Multiple CTE query definitions can be defined in a CTE.

  • CTE の後ろに SELECT ステートメントを 1 つ付ける必要があります。A CTE must be followed by a single SELECT statement. INSERTUPDATEDELETEMERGE ステートメントはサポートされていません。INSERT, UPDATE, DELETE, and MERGE statements are not supported.

  • それ自体の参照を含む共通テーブル式 (再帰共通テーブル式) はサポートされていません。A common table expression that includes references to itself (a recursive common table expression) is not supported.

  • 1 つの CTE の中で複数の WITH 句を指定することはできません。Specifying more than one WITH clause in a CTE is not allowed. たとえば、CTE_query_definition にサブクエリが含まれる場合、そのサブクエリには、別の CTE を定義する入れ子の WITH 句を含めることができません。For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

  • ORDER BY 句は、TOP 句が指定される場合を除き、CTE_query_definition で使用できません。An ORDER BY clause cannot be used in the CTE_query_definition, except when a TOP clause is specified.

  • バッチの一部となるステートメント内で CTE が使用される場合、この句の前のステートメントの末尾にセミコロンを記述する必要があります。When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

  • sp_prepare で与えられるステートメントで使用されるとき、CTE は PDW の他の SELECT ステートメントと同様に振る舞います。When used in statements prepared by sp_prepare, CTEs will behave the same way as other SELECT statements in PDW. ただし、CTE は sp_prepare で与えられる CETAS の一部として使用される場合、その振る舞いは、sp_prepare のバインドの実装方法に起因し、 SQL ServerSQL Server や他の PDW ステートメントとは異なることがあります。However, if CTEs are used as part of CETAS prepared by sp_prepare, the behavior can defer from SQL ServerSQL Server and other PDW statements because of the way binding is implemented for sp_prepare. CTE を参照する SELECT で CTE に存在しない間違った列が使用されている場合、sp_prepare はエラーを検出せずに通りますが、代わりに sp_execute 中にエラーがスローされます。If SELECT that references CTE is using a wrong column that does not exist in CTE, the sp_prepare will pass without detecting the error, but the error will be thrown during sp_execute instead.

使用例Examples

A.A. 単純な共通テーブル式を作成するCreating a simple common table expression

次の例は、 Adventure Works CyclesAdventure Works Cycles における販売員ごとの年間の販売注文数の合計を示しています。The following example shows the total number of sales orders per year for each sales representative at Adventure Works CyclesAdventure Works Cycles.


-- 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.B. 共通テーブル式を使用して、回数を制限し、平均数をレポートするUsing a common table expression to limit counts and report averages

次の例は、販売員のすべての年度の販売注文数の平均を示しています。The following example shows the average number of sales orders for all years for the sales representatives.

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.C. 単一のクエリでの複数の CTE の定義Using multiple CTE definitions in a single query

次の例は、単一のクエリで複数の CTE を定義する方法を示しています。The following example shows how to define more than one CTE in a single query. CTE クエリ定義を区切るために、コンマを使用することに注意してください。Notice that a comma is used to separate the CTE query definitions. 通貨書式で金額を表示する FORMAT 関数は、SQL Server 2012 以降で利用できます。The FORMAT function, used to display the monetary amounts in a currency format, is available in SQL Server 2012 and higher.


WITH Sales_CTE (SalesPersonID, TotalSales, SalesYear)  
AS  
-- Define the first CTE query.  
(  
    SELECT SalesPersonID, SUM(TotalDue) AS TotalSales, YEAR(OrderDate) AS SalesYear  
    FROM Sales.SalesOrderHeader  
    WHERE SalesPersonID IS NOT NULL  
       GROUP BY SalesPersonID, YEAR(OrderDate)  

)  
,   -- Use a comma to separate multiple CTE definitions.  

-- Define the second CTE query, which returns sales quota data by year for each sales person.  
Sales_Quota_CTE (BusinessEntityID, SalesQuota, SalesQuotaYear)  
AS  
(  
       SELECT BusinessEntityID, SUM(SalesQuota)AS SalesQuota, YEAR(QuotaDate) AS SalesQuotaYear  
       FROM Sales.SalesPersonQuotaHistory  
       GROUP BY BusinessEntityID, YEAR(QuotaDate)  
)  

-- Define the outer query by referencing columns from both CTEs.  
SELECT SalesPersonID  
  , SalesYear  
  , FORMAT(TotalSales,'C','en-us') AS TotalSales  
  , SalesQuotaYear  
  , FORMAT (SalesQuota,'C','en-us') AS SalesQuota  
  , FORMAT (TotalSales -SalesQuota, 'C','en-us') AS Amt_Above_or_Below_Quota  
FROM Sales_CTE  
JOIN Sales_Quota_CTE ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonID  
                    AND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear  
ORDER BY SalesPersonID, SalesYear;  
GO  

次に結果セットの一部を示します。Here is a partial result set.


SalesPersonID SalesYear   TotalSales    SalesQuotaYear SalesQuota  Amt_Above_or_Below_Quota  
------------- ---------   -----------   -------------- ---------- ----------------------------------   

274           2005        $32,567.92    2005           $35,000.00  ($2,432.08)  
274           2006        $406,620.07   2006           $455,000.00 ($48,379.93)  
274           2007        $515,622.91   2007           $544,000.00 ($28,377.09)  
274           2008        $281,123.55   2008           $271,000.00  $10,123.55  

D.D. 再帰共通テーブル式を使用して、複数の再帰レベルを表示するUsing a recursive common table expression to display multiple levels of recursion

次の例は、マネージャーおよびマネージャーにレポートする従業員の階層リストを示しています。The following example shows the hierarchical list of managers and the employees who report to them. 最初に、dbo.MyEmployees テーブルを作成して値を設定します。The example begins by creating and populating the dbo.MyEmployees table.

-- 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 AdventureWorks2012;  
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  

E.E. 再帰共通テーブル式を使用して、2 つの再帰レベルを表示するUsing a recursive common table expression to display two levels of recursion

次の例は、マネージャーおよびマネージャーにレポートする従業員を示しています。The following example shows managers and the employees reporting to them. 返されるレベルの数は 2 つに制限されます。The number of levels returned is limited to two.

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

F.F. 再帰共通テーブル式を使用して、階層リストを表示するUsing a recursive common table expression to display a hierarchical list

次の例は、例 D にマネージャーと従業員の名前および各自の役職を追加したものです。The following example builds on Example D by adding the names of the manager and employees, and their respective titles. 各レベルをインデントすることにより、マネージャーおよび従業員の階層をさらに強調しています。The hierarchy of managers and employees is additionally emphasized by indenting each level.

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

G.G. MAXRECURSION を使用して、ステートメントを取り消すUsing MAXRECURSION to cancel a statement

MAXRECURSION を使用すると、不適切に作成された再帰 CTE による無限ループの発生を防ぐことができます。MAXRECURSION can be used to prevent a poorly formed recursive CTE from entering into an infinite loop. 次の例では、無限ループを意図的に作成し、MAXRECURSION ヒントを使用して再帰レベルの数を 2 に制限しています。The following example intentionally creates an infinite loop and uses the MAXRECURSION hint to limit the number of recursion levels to two.

USE AdventureWorks2012;  
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 は不要になります。After the coding error is corrected, MAXRECURSION is no longer required. 次の例は、訂正されたコードを示しています。The following example shows the corrected code.

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

H.H. 共通テーブル式を使用して、SELECT ステートメント内の再帰リレーションシップを選択的にステップ スルーするUsing a common table expression to selectively step through a recursive relationship in a SELECT statement

次の例は、ProductAssemblyID = 800 の自転車を組み立てるのに必要な製品アセンブリとコンポーネントの階層を示しています。The following example shows the hierarchy of product assemblies and components that are required to build the bicycle for ProductAssemblyID = 800.

USE AdventureWorks2012;  
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.I. UPDATE ステートメントで再帰 CTE を使用するUsing a recursive CTE in an UPDATE statement

次の例は、製品 'Road-550-W Yellow, 44' (ProductAssemblyID``800) の製造に使用されるすべての部品の PerAssemblyQty 値を更新します。The following example updates the PerAssemblyQty value for all parts that are used to build the product 'Road-550-W Yellow, 44' (ProductAssemblyID``800). 共通テーブル式は、ProductAssemblyID 800 の製造に使用される部品およびこれらの部品の製造に使用されるコンポーネントの階層リストを返します。The common table expression returns a hierarchical list of parts that are used to build ProductAssemblyID 800 and the components that are used to create those parts, and so on. 共通テーブル式が返した行のみが変更されます。Only the rows returned by the common table expression are modified.

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

J.J. 複数のアンカー メンバーと再帰メンバーを使用するUsing multiple anchor and recursive members

次の例では、複数のアンカー メンバーと再帰メンバーを使用して、指定された個人のすべての先祖を返します。The following example uses multiple anchor and recursive members to return all the ancestors of a specified person. テーブルが 1 つ作成され、値が挿入されます。このテーブルは、再帰 CTE が返す家系図になります。A table is created and values inserted to establish the family genealogy returned by the recursive 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  

K.K. 再帰 CTE で分析関数を使用するUsing analytical functions in a recursive CTE

次の例は、CTE の再帰部分で分析関数または集計関数を使用するときに生じる可能性がある落とし穴を示しています。The following example shows a pitfall that can occur when using an analytical or aggregate function in the recursive part of a 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;  

次の結果は、クエリの予想結果です。The following results are the expected results for the query.

Lvl  N  
1    0  
1    0  
1    0  
1    0  
2    4  
2    3  
2    2  
2    1  

次の結果は、クエリの実際の結果です。The following results are the actual results for the query.

Lvl  N  
1    0  
1    0  
1    0  
1    0  
2    1  
2    1  
2    1  
2    1  

N は、CTE の再帰部分を通過するたびに 1 を返します。これは、その再帰レベルのデータのサブセットのみが ROWNUMBER に渡されるからです。N returns 1 for each pass of the recursive part of the CTE because only the subset of data for that recursion level is passed to ROWNUMBER. クエリの再帰部分を反復するたびに、1 つの行のみが ROWNUMBER に渡されます。For each of the iterations of the recursive part of the query, only one row is passed to ROWNUMBER.

例: SQL データ ウェアハウスSQL Data Warehouse および Parallel Data WarehouseParallel Data WarehouseExamples: SQL データ ウェアハウスSQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse

L.L. CTAS ステートメント内で共通テーブル式を使用するUsing a common table expression within a CTAS statement

次の例では、 Adventure Works CyclesAdventure Works Cycles における販売員ごとの年間の販売注文数の合計を含む新しいテーブルを作成しています。The following example creates a new table containing the total number of sales orders per year for each sales representative at Adventure Works CyclesAdventure Works Cycles.

-- Uses AdventureWorks  

CREATE TABLE SalesOrdersPerYear  
WITH  
(  
    DISTRIBUTION = HASH(SalesPersonID)  
)  
AS  
    -- 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  

M.M. CETAS ステートメント内で共通テーブル式を使用するUsing a common table expression within a CETAS statement

次の例では、 Adventure Works CyclesAdventure Works Cycles における販売員ごとの年間の販売注文数の合計を含む新しい外部テーブルを作成しています。The following example creates a new external table containing the total number of sales orders per year for each sales representative at Adventure Works CyclesAdventure Works Cycles.

-- Uses AdventureWorks  

CREATE EXTERNAL TABLE SalesOrdersPerYear  
WITH  
(  
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:5000/files/Customer',  
    FORMAT_OPTIONS ( FIELD_TERMINATOR = '|' )   
)  
AS  
    -- 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  

N.N. ステートメントでコンマ区切りの CTE を複数使用するUsing multiple comma separated CTEs in a statement

次の例では、1 つのステートメントで 2 つの CTE を使用しています。The following example demonstrates including two CTEs in a single statement. CTE は入れ子にできません (再帰なし)。The CTEs cannot be nested (no recursion).

WITH   
 CountDate (TotalCount, TableName) AS  
    (  
     SELECT COUNT(datekey), 'DimDate' FROM DimDate  
    ) ,  
 CountCustomer (TotalAvg, TableName) AS  
    (  
     SELECT COUNT(CustomerKey), 'DimCustomer' FROM DimCustomer  
    )  
SELECT TableName, TotalCount FROM CountDate  
UNION ALL  
SELECT TableName, TotalAvg FROM CountCustomer;  

参照See Also

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