適用対象:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Microsoft Fabric の SQL 分析エンドポイントMicrosoft Fabric WarehouseMicrosoft Fabric SQL Database
共通テーブル式 (CTE) と呼ばれる一時的な名前付き結果セットを指定します。 これは単純なクエリから派生し、単一の SELECT、 INSERT、 UPDATE、 MERGE、または DELETE ステートメントの実行スコープ内で定義されます。 この句は、定義SELECTステートメントの一部として、CREATE VIEW ステートメントでも使用できます。 共通テーブル式には、自己参照を含めることができます。 これは再帰共通テーブル式と呼ばれます。
構文
[ 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」を参照してください。
複数の CTE_query_definition が定義されている場合、クエリ定義は、 UNION ALL、 UNION、 EXCEPT、または INTERSECTのいずれかのセット演算子によって結合する必要があります。
使用ガイドライン
非回復的な共通テーブル式のガイドライン
注
非再帰共通テーブル式には、次のガイドラインが適用されます。 再帰的共通表式に適用されるガイドラインについては、再帰的共通 表式のガイドラインを参照してください。
CTE の後に、一部またはすべての CTE 列を参照する単一の SELECT、 INSERT、 UPDATE、 MERGE、または DELETE ステートメントが続く必要があります。 CTE は、ビューの CREATE VIEW ステートメントの定義の一部として SELECT ステートメントに指定することもできます。
非再帰 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句またはOFFSET/FETCH句が指定されている場合を除く) INTO-
OPTIONクエリ ヒント 1 を含む句 FOR BROWSE
1OPTION 句は CTE 定義内では使用できません。 最も外側の SELECT ステートメントでのみ使用できます。
バッチの一部となるステートメント内で CTE が使用される場合、この句の前のステートメントの末尾にセミコロンを記述する必要があります。
CTE を参照するクエリは、カーソルを定義するために使用できます。
リモート サーバー上のテーブルは、CTE 内で参照できます。
CTE を実行する場合、CTE を参照するヒントは、クエリ内のビューを参照するヒントと同じ方法で、CTE が基になるテーブルにアクセスしたときに検出される他のヒントと競合する可能性があります。 この競合が発生すると、クエリはエラーを返します。
再帰共通テーブル式のガイドライン
注
再帰共通テーブル式の定義には、次のガイドラインが適用されます。 非回復性 CTE に適用されるガイドラインについては、非 回復的共通表式のガイドラインを参照してください。
再帰 CTE の定義には、少なくとも 2 つの CTE クエリ定義を含める必要があります。1 つはアンカー メンバーで、もう 1 つは再帰メンバーです。 複数のアンカー メンバーと再帰メンバーを定義できます。ただし、アンカー メンバーの定義はすべて、最初の再帰メンバーの定義よりも前に記述する必要があります。 CTE 自体を参照しない CTE クエリ定義はすべてアンカー メンバーとなります。
アンカー メンバーは、 UNION ALL、 UNION、 INTERSECT、または EXCEPTのいずれかのセット演算子によって結合する必要があります。
UNION ALL は、最後のアンカー メンバーと最初の再帰メンバーの間、および複数の再帰メンバーを組み合わせて使用できる唯一の set 演算子です。
アンカー メンバーの列数と再帰メンバーの列数は、同じである必要があります。
再帰メンバーの列のデータ型は、アンカー メンバーの対応する列のデータ型と同じである必要があります。
再帰メンバーの FROM 句は、CTE の expression_name を一度だけ参照する必要があります。
次の項目は再帰メンバーの CTE_query_definition で許可されません。
SELECT DISTINCTGROUP BY-
PIVOT1 HAVING- スカラー集計
TOP-
LEFT、RIGHT、OUTER JOIN(INNER JOINは許可されている) - サブクエリ
- CTE_query_definition 内の CTE の再帰参照に適用されるヒント。
1 データベース互換性レベルが 110 以上の場合。 SQL Server 2016 のデータベース エンジン機能の破壊的変更を参照してください。
再帰共通テーブル式の使用には、次のガイドラインが適用されます。
再帰 CTE に含まれる
SELECTステートメントが返す列で NULL 値が許容されるかどうかにかかわらず、再帰 CTE が返すすべての列で NULL 値が許可されます。再帰 CTE が正しく構成されていないと、無限ループが発生する可能性があります。 たとえば、再帰メンバーのクエリ定義が親列と子列に対して同じ値を返す場合、無限ループが生成されます。 無限ループを回避するには、
INSERT、UPDATE、DELETE、またはSELECTステートメントのOPTION句の0と32767の間の値とMAXRECURSIONヒントを使用して、特定のステートメントに対して許可される再帰レベルの数を制限できます。 これにより、無限ループが作成される原因となったコードの問題が解決されるまで、ステートメントの実行を制御できます。 サーバー全体での既定値は 100 です。 0 を指定した場合、制限は適用されません。MAXRECURSIONの値は 1 つのステートメントに 1 つだけ指定できます。 詳細については、「クエリ ヒントの」を参照してください。 再帰共通テーブル式を含むビューを使用してデータを更新することはできません。
カーソルは、CTE を使用してクエリで定義できます。 CTE は、カーソルの結果セットを定義する select_statement 引数です。 再帰 CTE では、高速順方向専用および静的 (スナップショット) カーソルのみ使用できます。 他の種類のカーソルを再帰 CTE で指定した場合、カーソルの種類は静的に変換されます。
リモート サーバー上のテーブルは、CTE 内で参照できます。 CTE の再帰メンバーがリモート サーバーを参照する場合、各リモート テーブルごとにスプールが作成されます。そのため、ローカルからそのテーブルに繰り返しアクセスできます。 CTE クエリの場合、インデックス スプール/レイジー スプールはクエリ プランに表示され、追加の
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句を含めることができません。TOP句が指定されている場合を除き、ORDER BY句はCTE_query_definitionで使用できません。バッチの一部となるステートメント内で CTE が使用される場合、この句の前のステートメントの末尾にセミコロンを記述する必要があります。
sp_prepareによって準備されたステートメントで CTE を使用すると、APS PDW の他のSELECTステートメントと同じように動作します。 ただし、sp_prepareによって準備された CETAS の一部として CTE が使用されている場合、sp_prepareのバインドの実装方法により、SQL Server やその他の APS PDW ステートメントから動作が遅れる可能性があります。 CTE を参照するSELECTが CTE に存在しない間違った列を使用している場合、sp_prepareはエラーを検出せずに渡されますが、代わりにsp_execute中にエラーがスローされます。
例
Ａ。 共通テーブル式を作成する
次の例は、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 (11.x) で導入されました。
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
INNER 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
INNER 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
INNER 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
INNER 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
INNER 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
一 再帰 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
INNER JOIN vw AS t
ON t.itmID = r.itmIDComp
)
SELECT Lvl, N FROM r;
次の結果は、クエリの予想結果です。
Lvl N
1 0
1 0
1 0
1 0
2 4
2 3
2 2
2 1
次の結果は、クエリの実際の結果です。
Lvl N
1 0
1 0
1 0
1 0
2 1
2 1
2 1
2 1
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;