logo

TechCodeview

CTE in SQL

Der Gemeinsame Tabellenausdrücke (CTE) wurden in Standard-SQL eingeführt, um verschiedene Klassen von SQL-Abfragen zu vereinfachen, für die eine abgeleitete Tabelle einfach ungeeignet war. CTE wurde in SQL Server 2005 eingeführt. Der Common Table Expression (CTE) ist eine temporäre benannte Ergebnismenge, auf die Sie innerhalb einer Tabelle verweisen können WÄHLEN , EINFÜGEN , AKTUALISIEREN , oder LÖSCHEN Stellungnahme. Sie können einen CTE auch in einer CREATE-Ansicht als Teil der SELECT-Abfrage der Ansicht verwenden. Darüber hinaus können Sie ab SQL Server 2008 der neuen MERGE-Anweisung einen CTE hinzufügen.

Warum brauchen wir CTE im SQL Server?

Eine gemeinsame Tabelle [veraltet]CTE ist eine leistungsstarke SQL Konstrukt, das hilft, Abfragen zu vereinfachen. CTEs fungieren als virtuelle Tabellen (mit Datensätzen und Spalten), die während der Abfrageausführung erstellt, von der Abfrage verwendet und nach der Ausführung der Abfrage gelöscht werden.

Verwendung des CTE

Wir können CTEs definieren, indem wir eine WITH-Klausel direkt vor der SELECT-, INSERT-, UPDATE-, DELETE- oder MERGE-Anweisung hinzufügen. Die WITH-Klausel kann einen oder mehrere durch Kommas getrennte CTEs enthalten.



Apache

Syntax:

[MIT [, …]]

::=

cte_name [(column_name [, …])]

Kern-Java-Sprache

AS (cte_query)

Streit

  1. Ausdrucksname: Ein gültiger Bezeichner für einen allgemeinen Tabellenausdruck. Der Ausdrucksname muss sich von den Namen anderer allgemeiner Tabellenausdrücke unterscheiden, die in derselben WITH-Klausel definiert sind, aber der Ausdrucksname kann mit dem Namen der Basistabelle oder -ansicht identisch sein. Alle Verweise auf expression_name in der Abfrage verwenden den allgemeinen Tabellenausdruck anstelle des Basisobjekts.
  2. Spaltenname: Gibt einen Spaltennamen in einem allgemeinen Tabellenausdruck an. Doppelte Namen innerhalb einer einzelnen CTE-Definition sind nicht zulässig. Die Anzahl der Spaltennamen muss mit der Anzahl der Spalten im Ergebnissatz der CTE_query_definition übereinstimmen. Eine Liste mit Spaltennamen ist nur dann optional, wenn alle resultierenden Spalten in der Abfragedefinition unterschiedliche Namen haben.
  3. CTE_QueryDefinition: Gibt eine SELECT-Anweisung an, deren Ergebnismenge einen allgemeinen Tabellenausdruck erfüllt. Die SELECT-Anweisung für CTE_query_defining muss dieselben Anforderungen erfüllen wie das Erstellen einer Ansicht, mit der Ausnahme, dass ein CTE keinen anderen CTE definieren kann. Weitere Informationen finden Sie im Abschnitt „Hinweise“ und unter „CREATE VIEW (Transact-SQL)“. Wenn mehrere CTE_query_settings definiert sind, müssen die Abfragedefinitionen mit einem der Mengenoperatoren UNION ALL, UNION, EXCEPT oder INTERSECT kombiniert werden.

Regeln für die Definition und Verwendung rekursiver gemeinsamer Tabellenausdrücke

Die folgenden Richtlinien gelten für die Definition rekursiver allgemeiner Tabellenausdrücke:

  1. Eine rekursive CTE-Definition muss mindestens zwei CTE-Abfragedefinitionen enthalten, ein Ankermitglied und ein rekursives Mitglied. Sie können mehrere Anker- und rekursive Mitglieder definieren. Allerdings müssen alle Anker-Member-Abfragedefinitionen vor der ersten rekursiven Member-Definition platziert werden. Alle CTE-Abfragedefinitionen sind Ankermitglieder, es sei denn, sie verweisen auf den CTE selbst.
  2. Ankerelemente müssen mit einem der folgenden Mengenoperatoren kombiniert werden: UNION ALL, UNION, INTERSECT, EXCEPT. UNION ALL ist der einzige gültige Mengenoperator zwischen dem letzten Ankermitglied und dem ersten rekursiven Mitglied, wenn mehrere rekursive Mitglieder verbunden werden. Anker- und rekursive Elemente müssen die gleiche Anzahl von Spalten haben.
  3. Der Datentyp der Spalten des rekursiven Mitglieds muss mit dem Datentyp der entsprechenden Spalte des Ankermitglieds identisch sein.
  4. Die folgenden Elemente sind in ihrer CTE_query_definition für rekursive Mitglieder nicht zulässig:
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.>

Erstellen eines rekursiven allgemeinen Tabellenausdrucks

Ein rekursiver CTE ist ein CTE, der sich innerhalb dieses CTE selbst referenziert. Der rekursive CTE ist nützlich, wenn mit hierarchischen Daten gearbeitet wird, da der CTE so lange ausgeführt wird, bis die Abfrage die gesamte Hierarchie zurückgibt.

Ein typisches Beispiel für hierarchische Daten ist eine Tabelle, die eine Liste von Mitarbeitern enthält. Für jeden Mitarbeiter enthält die Tabelle einen Verweis auf den Vorgesetzten dieser Person. Diese Referenz ist selbst eine Mitarbeiter-ID innerhalb derselben Tabelle. Sie können einen rekursiven CTE verwenden, um die Hierarchie der Mitarbeiterdaten anzuzeigen.

Wenn ein CTE falsch erstellt wird, kann er in eine Endlosschleife geraten. Um dies zu verhindern, kann der MAXRECURSION-Hinweis in der OPTION-Klausel der primären SELECT-, INSERT-, UPDATE-, DELETE- oder MERGE-Anweisung hinzugefügt werden.

Eine Tabelle wird erstellt:

wie man das Skript unter Linux ausführt 
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)>

Nachdem die Employees-Tabelle erstellt wurde, wird die folgende SELECT-Anweisung erstellt, der eine WITH-Klausel vorangestellt ist, die einen CTE namens cteReports enthält:

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>

Daher können CTEs nützlich sein, wenn Sie temporäre Ergebnismengen generieren müssen, auf die in einer SELECT-, INSERT-, UPDATE-, DELETE- oder MERGE-Anweisung zugegriffen werden kann.

Funktionen und Einschränkungen allgemeiner Tabellenausdrücke in Azure

Die aktuelle Implementierung von CTEs in Azure Synapse Analytics und Analytics Platform System (PDW) weist die folgenden Funktionen und Einschränkungen auf:

  1. Ein CTE kann nur in einer SELECT-Anweisung angegeben werden.
  2. Ein CTE kann nur in einer CREATE VIEW-Anweisung angegeben werden.
  3. Ein CTE kann nur in einer CREATE TABLE AS SELECT (CTAS)-Anweisung angegeben werden.
  4. Ein CTE kann nur in einer CREATE REMOTE TABLE AS SELECT (CRTAS)-Anweisung angegeben werden.
  5. Ein CTE kann nur in einer CREATE EXTERNAL TABLE AS SELECT (CETAS)-Anweisung angegeben werden.