logo

TechCodeview

SQL の CTE

の 共通テーブル式 (CTE) 派生テーブルが適さないさまざまなクラスの SQL クエリを簡素化するために、標準 SQL に導入されました。 CTE は SQL Server 2005 で導入されました。共通テーブル式 (CTE) は、ファイル内で参照できる一時的な名前付き結果セットです。 選択する 、 入れる 、 アップデート 、 または 消去 声明。 CREATE ビューで、ビューの SELECT クエリの一部として CTE を使用することもできます。さらに、SQL Server 2008 では、新しい MERGE ステートメントに CTE を追加できます。

SQL サーバーに CTE が必要なのはなぜですか?

共通テーブル [非推奨]CTE は強力な SQL クエリを簡素化するのに役立つ構造。 CTE は、クエリの実行中に作成され、クエリによって使用され、クエリの実行後に削除される仮想テーブル (レコードと列を含む) として機能します。

CTE の使用

CTE を定義するには、SELECT、INSERT、UPDATE、DELETE、または MERGE ステートメントの直前に WITH 句を追加します。 WITH 句には、カンマで区切られた 1 つ以上の CTE を含めることができます。



Pythonの剰余演算子

構文：

[と [、 …]]

::=

cte_name [(column_name [, …])]

AS (cte_query)

口論

  1. 式名: 共通テーブル式の有効な識別子。式名は、同じ WITH 句で定義されている他の共通テーブル式の名前とは異なる必要がありますが、式名はベース テーブルまたはビューの名前と同じであってもかまいません。クエリ内のexpression_nameへのすべての参照では、基本オブジェクトの代わりに共通のテーブル式が使用されます。
  2. 列名: 共通テーブル式の列名を指定します。単一の CTE 定義内で重複する名前は許可されません。列名の数は、CTE_query_definition の結果セット内の列の数と一致する必要があります。列名のリストは、クエリ定義内の結果として得られるすべての列の名前が異なる場合にのみオプションです。
  3. CTE_クエリ定義: 結果セットが共通テーブル式を満たす SELECT ステートメントを指定します。 CTE_query_defining の SELECT ステートメントは、CTE が別の CTE を定義できないことを除き、ビューの作成と同じ要件を満たす必要があります。詳細については、「備考」セクションと「CREATE VIEW (Transact-SQL)」を参照してください。複数の CTE_query_settings が定義されている場合は、クエリ定義を集合演算子 UNION ALL、UNION、EXCEPT、または INTERSECT の 1 つと組み合わせる必要があります。

再帰的共通テーブル式の定義と使用のルール

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

  1. 再帰的 CTE 定義には、少なくとも 2 つの CTE クエリ定義、アンカー メンバーと再帰的メンバーが含まれている必要があります。複数のアンカーメンバーと再帰メンバーを定義できます。ただし、すべてのアンカー メンバーのクエリ定義は、最初の再帰メンバー定義の前に配置する必要があります。 CTE 自体を参照しない限り、すべての CTE クエリ定義はアンカー メンバーです。
  2. アンカー要素は、UNION ALL、UNION、INTERSECT、EXCEPT のいずれかの集合演算子と組み合わせる必要があります。 UNION ALL は、複数の再帰メンバーを結合する場合、最後のアンカー メンバーと最初の再帰メンバーの間で唯一有効な集合演算子です。アンカー要素と再帰要素には同じ数の列が必要です。
  3. 再帰メンバーの列のデータ型は、アンカー メンバーの対応する列のデータ型と同じである必要があります。
  4. 次の項目は、再帰メンバーの CTE_query_setting では許可されません。
Please choose a different grouping PIVOT (For database compatibility level 110 and above.  See Breaking Changes to Database Engine Features in SQL Server 2016. Offal Scalar aggregation Up LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed) subquery A hint applied to recursive references to CTEs within a CTE_query_definition.>

再帰的な共通テーブル式の作成

再帰的 CTE は、その CTE 内でそれ自体を参照する CTE です。再帰的 CTE は、クエリが階層全体を返すまで実行を続けるため、階層データを操作する場合に便利です。

in.next Java

階層データの典型的な例は、従業員のリストを含むテーブルです。すべての従業員について、テーブルはその人のマネージャーへの参照を提供します。この参照自体は、同じテーブル内の従業員 ID です。再帰的 CTE を使用して、従業員データの階層を表示できます。

CTE が誤って作成されると、無限ループに陥る可能性があります。これを防ぐために、プライマリ SELECT、INSERT、UPDATE、DELETE、または MERGE ステートメントの OPTION 句に MAXRECURSION ヒントを追加できます。

テーブルが作成されます。 

CREATE TABLE Employees (  EmployeeID int NOT NULL PRIMARY KEY,  FirstName varchar(50) NOT NULL,  LastName varchar(50) NOT NULL,  ManagerID int NULL )  INSERT INTO Employees VALUES (1, 'Ken', 'Thompson', NULL) INSERT INTO Employees VALUES (2, 'Terri', 'Ryan', 1) INSERT INTO Employees VALUES (3, 'Robert', 'Durello', 1) INSERT INTO Employees VALUES (4, 'Rob', 'Bailey', 2) INSERT INTO Employees VALUES (5, 'Kent', 'Erickson', 2) INSERT INTO Employees VALUES (6, 'Bill', 'Goldberg', 3) INSERT INTO Employees VALUES (7, 'Ryan', 'Miller', 3) INSERT INTO Employees VALUES (8, 'Dane', 'Mark', 5) INSERT INTO Employees VALUES (9, 'Charles', 'Matthew', 6) INSERT INTO Employees VALUES (10, 'Michael', 'Jhonson', 6)>

Employees テーブルが作成された後、cteReports という名前の CTE を含む WITH 句が前に置かれた次の SELECT ステートメントが作成されます。

WITH  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)  AS  (  SELECT EmployeeID, FirstName, LastName, ManagerID, 1  FROM Employees  WHERE ManagerID IS NULL  UNION ALL  SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,   r.EmpLevel + 1  FROM Employees e  INNER JOIN cteReports r  ON e.ManagerID = r.EmpID  ) SELECT  FirstName + ' ' + LastName AS FullName,   EmpLevel,  (SELECT FirstName + ' ' + LastName FROM Employees   WHERE EmployeeID = cteReports.MgrID) AS Manager FROM cteReports  ORDER BY EmpLevel, MgrID>

したがって、CTE は、SELECT、INSERT、UPDATE、DELETE、または MERGE ステートメントでアクセスできる一時的な結果セットを生成する必要がある場合に役立ちます。

CSSの中央の画像

Azure の共通テーブル式の機能と制限事項

Azure Synapse Analytics および Analytics Platform System (PDW) での CTE の現在の実装には、次の機能と制限があります。

  1. CTE は SELECT ステートメントでのみ指定できます。
  2. CTE は CREATE VIEW ステートメントでのみ指定できます。
  3. CTE は、CREATE TABLE AS SELECT (CTAS) ステートメントでのみ指定できます。
  4. CTE は、CREATE REMOTE TABLE AS SELECT (CRTAS) ステートメントでのみ指定できます。
  5. CTE は、CREATE EXTERNAL TABLE AS SELECT (CETAS) ステートメントでのみ指定できます。