WITH common_table_expression (Transact-SQL)

[アーティクル]
07/19/2024

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric の SQL 分析エンドポイント Microsoft Fabric Warehouse Microsoft Fabric SQL Database

共通テーブル式 (CTE) と呼ばれる一時的な名前付き結果セットを指定します。これは単純なクエリから派生し、単一の SELECT、INSERT、UPDATE、DELETE、または MERGE ステートメントの実行スコープ内で定義されます。この句は、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 が定義されている場合、次の set 演算子のいずれかでクエリ定義を結合する必要があります:UNION ALL、UNION、EXCEPT、INTERSECT。

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

非再帰共通テーブル式には、次のガイドラインが適用されます。再帰共通テーブル式に適用されるガイドラインについては、後述の「再帰共通テーブル式の定義および使用に関するガイドライン」を参照してください。

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

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

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

再帰 CTE の定義には、少なくとも 2 つの CTE クエリ定義を含める必要があります。1 つはアンカーメンバーで、もう 1 つは再帰メンバーです。複数のアンカーメンバーと再帰メンバーを定義できます。ただし、アンカーメンバーの定義はすべて、最初の再帰メンバーの定義よりも前に記述する必要があります。 CTE 自体を参照しない CTE クエリ定義はすべてアンカーメンバーとなります。
アンカーメンバーは、次の set 演算子のいずれかによって結合する必要があります:UNION ALL、UNION、INTERSECT、EXCEPT。 UNION ALL は、最後のアンカーメンバーと最初の再帰メンバーを連結する場合、および複数の再帰メンバーを連結する場合に使用できる唯一の set 演算子です。
アンカーメンバーの列数と再帰メンバーの列数は、同じである必要があります。
再帰メンバーの列のデータ型は、アンカーメンバーの対応する列のデータ型と同じである必要があります。
再帰メンバーの FROM 句は、CTE の expression_name を一度だけ参照する必要があります。
次の項目は再帰メンバーの CTE_query_definition で許可されません。
- SELECT DISTINCT
- GROUP BY
- PIVOT (データベースの互換性レベルが 110 以上の場合は、「SQL Server 2016 におけるデータベースエンジン機能の重大な変更」を参照してください。)
- HAVING
- スカラー集計
- TOP
- LEFT、RIGHT、OUTER JOIN (INNER JOIN は許可されている)
- サブクエリ
- CTE_query_definition 内の 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 の再帰メンバーがリモートサーバーを参照する場合、各リモートテーブルごとにスプールが作成されます。そのため、ローカルからそのテーブルに繰り返しアクセスできます。 CTE クエリの場合、クエリプランに Index Spool/Lazy Spool が表示され、WITH STACK 述語が付加されます。これは、適切な再帰を確認する方法の 1 つです。
CTE の再帰部分の分析関数と集計関数は、CTE のセットではなく、現在の再帰レベルのセットに適用されます。 ROW_NUMBER などの関数は、現在の再帰レベルによって渡されたデータのサブセットでのみ機能し、CTE の再帰部分に渡されたデータのセット全体では機能しません。詳細については、「I. 再帰 CTE で分析関数を使用する」を参照してください。

Azure Synapse Analytics and Analytics Platform System (PDW) の一般的なテーブル式

Azure Synapse Analytics and Analytics Platform System (PDW) での CTE の現在の実装には、次の機能と要件があります。

CTE は SELECT ステートメントに指定できます。
CTE は CREATE VIEW ステートメントに指定できます。
CTE は CREATE TABLE AS SELECT (CTAS) ステートメントに指定できます。
CTE は CREATE REMOTE TABLE AS SELECT (CRTAS) ステートメントに指定できます。
CTE は CREATE EXTERNAL TABLE AS SELECT (CETAS) ステートメントに指定できます。
リモートテーブルは CTE から参照できます。
外部テーブルは CTE から参照できます。
CTE では、複数の CTE クエリを定義できます。
CTE の後には、 SELECT、 INSERT、 UPDATE、 DELETE、または MERGE ステートメントを指定できます。
それ自体の参照を含む共通テーブル式 (再帰共通テーブル式) はサポートされていません。
1 つの CTE の中で複数の WITH 句を指定することはできません。たとえば、CTE クエリ定義にサブクエリが含まれる場合、そのサブクエリには、別の CTE を定義する入れ子の WITH 句を含めることができません。
ORDER BY 句は、TOP 句が指定される場合を除き、CTE_query_definition で使用できません。
バッチの一部となるステートメント内で CTE が使用される場合、この句の前のステートメントの末尾にセミコロンを記述する必要があります。
sp_prepareによって準備されたステートメントで CTE を使用する場合、CTE は APS PDW の他のSELECTステートメントと同じように動作します。ただし、 sp_prepareによって準備された CETAS の一部として CTE が使用されている場合、 sp_prepareのバインドの実装方法により、SQL Server やその他の APS PDW ステートメントから動作が遅れる可能性があります。 CTE を参照する SELECT で CTE に存在しない間違った列が使用されている場合、sp_prepare はエラーを検出せずに通りますが、代わりに sp_execute 中にエラーがスローされます。

例

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

次の例は、Adventure 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;

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;

C. 単一のクエリで複数の CTE 定義を使用する

次の例は、単一のクエリで複数の CTE を定義する方法を示しています。 CTE クエリ定義を区切るには、コンマを使用します。通貨形式で金額を表示するために使用される FORMAT 関数は、SQL Server 2012 以降のバージョンで使用できます。

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;

次に結果セットの一部を示します。

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. 再帰共通テーブル式を使用して、複数の再帰レベルを表示する

次の例は、マネージャーおよびそのマネージャーにレポートする従業員の階層リストを示しています。最初に、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 SMALLINT NULL,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC),
CONSTRAINT FK_MyEmployees_ManagerID_EmployeeID FOREIGN KEY (ManagerID) REFERENCES dbo.MyEmployees (EmployeeID)
);
-- 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);

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;

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

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

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 ;

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

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

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;

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

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

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

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

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;

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

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

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

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

次の例は、製品 'Road-550-W Yellow, 44' (ProductAssemblyID``800) の製造に使用されるすべての部品の PerAssemblyQty 値を更新します。共通テーブル式は、ProductAssemblyID 800 の製造に使用される部品およびこれらの部品の製造に使用されるコンポーネントの階層リストを返します。共通テーブル式が返した行のみが変更されます。

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

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

次の例では、複数のアンカーメンバーと再帰メンバーを使用して、指定された個人のすべての先祖を返します。テーブルが 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

I. 再帰 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 に渡されます。

例: Azure Synapse Analytics、Analytics Platform System (PDW)

J. CTAS ステートメント内で共通テーブル式を使用する

次の例では、Adventure Works Cycles における販売員ごとの年間の販売注文数の合計を含む新しいテーブルを作成しています。

USE AdventureWorks2022;
GO
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

K. CETAS ステートメント内で共通テーブル式を使用する

次の例では、Adventure Works Cycles における販売員ごとの年間の販売注文数の合計を含む新しい外部テーブルを作成しています。

USE AdventureWorks2022;
GO
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

L. ステートメントでコンマ区切りの CTE を複数使用する

次の例では、1 つのステートメントで 2 つの CTE を使用しています。 CTE は入れ子にできません (再帰なし)。

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;

次の方法で共有

WITH common_table_expression (Transact-SQL)

構文

引数

expression_name

column_name

CTE_query_definition

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

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

Azure Synapse Analytics and Analytics Platform System (PDW) の一般的なテーブル式

例

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

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

C. 単一のクエリで複数の CTE 定義を使用する

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

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

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

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

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

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

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

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

例: Azure Synapse Analytics、Analytics Platform System (PDW)

J. CTAS ステートメント内で共通テーブル式を使用する

K. CETAS ステートメント内で共通テーブル式を使用する

L. ステートメントでコンマ区切りの CTE を複数使用する

フィードバック

その他のリソース

次の方法で共有

WITH common_table_expression (Transact-SQL)

構文

引数

expression_name

column_name

CTE_query_definition

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

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

Azure Synapse Analytics and Analytics Platform System (PDW) の一般的なテーブル式

例

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

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

C. 単一のクエリで複数の CTE 定義を使用する

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

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

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

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

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

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

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

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

例: Azure Synapse Analytics、Analytics Platform System (PDW)

J. CTAS ステートメント内で共通テーブル式を使用する

K. CETAS ステートメント内で共通テーブル式を使用する

L. ステートメントでコンマ区切りの CTE を複数使用する

関連するコンテンツ

フィードバック

その他のリソース

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

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