Der Common Table Expression (CTE) wurde in SQL Server 2005 eingeführt und ist eine temporäre benannte Ergebnismenge, auf die Sie innerhalb einer SELECT -, INSERT -, UPDATE-oder DELETE-Anweisung verweisen können. Sie können eine CTE auch in einer CREATE VIEW-Anweisung 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.
SQL Server unterstützt zwei Arten von CTEs-rekursiv und nicht rekursiv. In diesem Artikel erkläre ich, wie beide Typen erstellt werden., Die von mir bereitgestellten Beispiele basieren auf einer lokalen Instanz von SQL Server 2008 und rufen Daten aus der AdventureWorks2008-Beispieldatenbank ab.
Arbeiten mit allgemeinen Tabellenausdrücken
Sie definieren CTEs, indem Sie direkt vor Ihrer SELECT -, INSERT -, UPDATE -, DELETE-oder MERGE-Anweisung eine WITH-Klausel hinzufügen.,“>
|
1
2
3
4
5
|
]
<common_table_expression>::=
cte_name )]
AS (cte_query)
|
…which can be represented like this…
As you can see, if you include more than one CTE in your WITH clause, you must separate them with commas., Außerdem müssen Sie für jede CTE einen Namen, das Schlüsselwort AS und eine SELECT-Anweisung angeben. Sie können auch Spaltennamen (durch Kommas getrennt) angeben, solange die Anzahl der Namen mit der Anzahl der von der Ergebnismenge zurückgegebenen Spalten übereinstimmt.
Die SELECT-Anweisung in Ihrer CTE-Abfrage muss denselben Anforderungen entsprechen wie die zum Erstellen einer Ansicht verwendeten. Einzelheiten zu diesen Anforderungen finden Sie im Thema „ANSICHT ERSTELLEN (Transact-SQL)“ in SQL Server Books Online. Weitere Informationen zu CTEs im Allgemeinen finden Sie im Thema „MIT common_table_expression (Transact-SQL).,“
Nachdem Sie Ihre WITH Klausel mit den erforderlichen CTEs definiert haben, können Sie diese CTEs wie jede andere Tabelle referenzieren. Sie können jedoch nur innerhalb des Ausführungsbereichs der Anweisung, die unmittelbar auf die WITH Klausel folgt, auf eine CTE verweisen. Nachdem Sie Ihre Anweisung ausgeführt haben, ist die CTE-Ergebnismenge für andere Anweisungen nicht verfügbar.
Erstellen eines nicht rekursiven allgemeinen Tabellenausdrucks
Ein nicht rekursiver CTE verweist nicht auf sich selbst innerhalb des CTE. Nicht rekursive CTEs sind in der Regel einfacher als rekursive CTEs, weshalb ich mit diesem Typ beginne.,cteTotalSales:
Nachdem ich den CTE-Namen angegeben habe, gebe ich zwei Spaltennamen an, SalesPersonID und NetSales, die in Klammern eingeschlossen und durch ein Komma getrennt sind. Das bedeutet, dass die von der CTE-Abfrage zurückgegebene Ergebnismenge zwei Spalten zurückgeben muss.
Als nächstes gebe ich das Schlüsselwort AS und dann eine Reihe von Klammern an, die die CTE-Abfrage einschließen. In diesem Fall gibt die SELECT-Anweisung den Gesamtumsatz für jede Verkaufsperson zurück (Gesamtumsatz gruppiert nach Verkäufer-ID)., Wie Sie sehen, kann die CTE-Abfrage Transact-SQL-Funktionen, GROUP BY-Klauseln oder alle Elemente enthalten, die die SELECT-Anweisung in einer Ansichtsdefinition enthalten kann.
Ich kann jetzt in der folgenden Anweisung auf cteTotalSales verweisen. In diesem Beispiel erstelle ich eine SELECT-Anweisung, die den Verkäufen beitritt.vSalesPerson Ansicht cteTotalSales, basierend auf der Verkäufer-ID. Ich ziehe dann die Namen und Orte aus der Ansicht und den Nettoumsatz aus der CTE. Die folgende Tabelle zeigt die von dieser Anweisung zurückgegebenen Ergebnisse.,
Wie Sie bereits in der Syntax gesehen haben, können Sie mehrere CTEs in eine WITH-Klausel einfügen.,
div>
Der erste CTE-cteTotalSales-ähnelt dem im vorherigen Beispiel, mit der Ausnahme, dass die WHERE-Klausel nur ab 2003 weiter qualifiziert wurde, um Verkäufe einzuschließen. Nachdem ich cteTotalSales definiert habe, füge ich ein Komma hinzu und definiere dann cteTargetDiff, das die Differenz zwischen der Umsatzsumme und der Umsatzquote berechnet.
Die neue CTE-Definition gibt drei Spalten für die Ergebnismenge an: SalesPersonID, SalesQuota und QuotaDiff., Wie zu erwarten, gibt die CTE-Abfrage drei Spalten zurück. Die erste ist die Verkäufer-ID. Die zweite ist die Verkaufsquote. Da jedoch ein Verkaufskontingent für einige Verkäufer nicht definiert ist, verwende ich eine CASE-Anweisung. Wenn der Wert null ist, wird dieser Wert auf 0 gesetzt, andernfalls wird der tatsächliche SalesQuota-Wert verwendet.
Die zurückgegebene letzte Spalte ist die Differenz zwischen Nettoumsatz und Verkaufsquote. Auch hier verwende ich eine CASE-Anweisung. Wenn der SalesQuota-Wert null ist, wird der NetSales-Wert verwendet, andernfalls wird das Sales-Kontingent vom Nettoumsatz subtrahiert, um zur Differenz zu gelangen.,
Etwas Interessantes an der zweiten CTE-Abfrage ist, dass ich dem Verkauf beigetreten bin.Verkäufer Tabelle zum ersten CTE-cteTotalSales-damit ich die Differenz zwischen dem Gesamtumsatz und der Verkaufsquote berechnen kann. Wenn Sie mehrere CTEs in einer einzelnen WITH Klausel definieren, können Sie auf vorherige CTEs verweisen (aber nicht umgekehrt).
Sobald ich meine CTEs definiert habe, kann ich sie in der ersten Anweisung referenzieren, die dem CTE folgt, wie Sie im vorherigen Beispiel gesehen haben. In diesem Fall schließe ich mich dem Verkauf an.,vSalesPerson Ansicht cteTotalSales und dann verbinden cteTargetDiff, alle basierend auf der Verkäufer-ID. Meine Auswahlliste enthält dann Spalten aus allen drei Quellen. Die Anweisung gibt die in der folgenden Tabelle gezeigten Ergebnisse zurück.
Wie Sie sehen, werden Verkaufsdaten für alle Verkäufer bereitgestellt, einschließlich der Stadt, in der sie wohnen, ihres Nettoumsatzes, ihrer Verkaufsquote und der berechneten Differenz zwischen den beiden Zahlen. In diesem Fall übersteigt jeder die Quote, in der eine Quote definiert wurde, deutlich.,
Erstellen eines rekursiven gemeinsamen Tabellenausdrucks
Ein rekursiver CTE verweist innerhalb dieses CTE auf sich selbst. Der rekursive CTE ist nützlich, wenn Sie mit hierarchischen Daten arbeiten, 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 Manager dieser Person. Diese Referenz ist selbst eine Mitarbeiter-ID in derselben Tabelle., Sie können eine rekursive CTE verwenden, um die Hierarchie der Mitarbeiterdaten anzuzeigen, wie sie im Organigramm angezeigt wird.
Beachten Sie, dass eine falsch erstellte CTE in eine Endlosschleife eintreten kann. Um dies zu verhindern, können Sie den Hinweis MAXRECURSION in die Optionsklausel der Anweisung primary SELECT, INSERT, UPDATE, DELETE oder MERGE einfügen. Informationen zur Verwendung von Abfragehinweisen finden Sie im Thema „Abfragehinweise (Transact-SQL)“ in SQL Server Books Online.,
Um zu demonstrieren, wie das rekursive CTE funktioniert, habe ich die folgenden Transact-SQL-Anweisungen verwendet, um die Employees-Tabelle in der AdventureWorks2008-Datenbank zu erstellen und zu füllen:
Wie Sie vielleicht erkennen, enthält die AdventureWorks2008-Datenbank bereits die HumanResources.Mitarbeiter-Tabelle. Diese Tabelle verwendet jedoch jetzt den Datentyp hierarchyid zum Speichern hierarchischer Daten, was zu unnötiger Komplexität führen würde, wenn versucht wird, einen rekursiven CTE zu demonstrieren. Aus diesem Grund habe ich meine eigene Tabelle erstellt., Wenn Sie jedoch eine rekursive CTE ausprobieren möchten, ohne eine neue Tabelle zu erstellen und zu füllen, können Sie die AdventureWorks-Beispieldatenbank verwenden, die mit SQL Server 2005 geliefert wurde. Die Humanressourcen.Die Employee-Tabelle in dieser Datenbank speichert die Daten auf ähnliche Weise wie die Tabelle, die ich oben erstellt habe.,div>2
Wie Sie sehen können, gibt der CTE fünf Spalten zurück: EmpID, Vorname, Nachname, MgrID und EmpLevel. Die EmpLevel-Spalte bezieht sich auf die Ebene in der Hierarchie, in die die Mitarbeiter passen. Die höchste Ebene der Hierarchie ist 1, die nächste Ebene ist 2, gefolgt von 3, und so weiter.
Die CTE-Abfrage selbst besteht aus zwei SELECT-Anweisungen, die mit dem Operator UNION ALL verbunden sind., Eine rekursive CTE-Abfrage muss mindestens zwei Member (Anweisungen) enthalten, die durch den Operator UNION ALL, UNION, INTERSECT oder EXCEPT verbunden sind. In diesem Beispiel ist die erste SELECT-Anweisung das Ankerelement und die zweite Anweisung das rekursive Element. Alle Ankerelemente müssen den rekursiven Elementen vorausgehen, und nur die rekursiven Elemente können auf die CTE selbst verweisen. Darüber hinaus müssen alle Mitglieder die gleiche Anzahl von Spalten mit entsprechenden Datentypen zurückgeben.
Schauen wir uns nun die Aussagen selbst genauer an., Die erste Anweisung, das Ankerelement, ruft die Mitarbeiter-ID, den Vornamen, den Nachnamen und die Manager-ID aus der Employees-Tabelle ab, wobei die Manager-ID null ist. Dies wäre der Mitarbeiter an der Spitze der Hierarchie, was bedeutet, dass diese Person niemandem berichtet. Folglich ist der Manager-ID-Wert null. Um zu reflektieren, dass diese Person ganz oben in der Hierarchie steht, weise ich der EmpLevel-Spalte den Wert 1 zu.
Die zweite Anweisung in der CTE-Abfrage-das rekursive Element-ruft auch die Mitarbeiter-ID, den Vornamen, den Nachnamen und die Manager-ID für Mitarbeiter in der Employees-Tabelle ab., Beachten Sie jedoch, dass ich die Employees-Tabelle mit der CTE selbst verbinde. Darüber hinaus basiert der Join auf der Manager-ID in der Employees-Tabelle und der Employee-ID in der CTE. Auf diese Weise durchläuft der CTE die Employees-Tabelle, bis er die gesamte Hierarchie zurückgibt.
Ein weiteres Element, das bei der zweiten Anweisung zu beachten ist, ist, dass ich für die EmpLevel-Spalte den Wert 1 zum EmpLevel-Wert hinzufüge, wie er im CTE angezeigt wird. Auf diese Weise wird jedes Mal, wenn die Anweisung die Hierarchie durchläuft, die nächste richtige Ebene auf die Mitarbeiter auf der Ebene angewendet.,
Nachdem ich meine WITH Klausel definiert habe, erstelle ich eine SELECT Anweisung, die die Daten aus dem CTE abruft. Beachten Sie jedoch, dass ich für die Spalte Manager den Vor-und Nachnamen des Mitarbeiters abrufe, der der Manager-ID in der CTE zugeordnet ist. Auf diese Weise kann ich den vollständigen Namen des Managers für jeden Mitarbeiter anzeigen. Die folgende Tabelle zeigt die Ergebnismenge, die von der SELECT-Anweisung und ihrer CTE zurückgegeben wird.,
Wie Sie sehen können, kann der CTE, ob rekursiv oder nicht rekursiv, ein nützliches Werkzeug sein, wenn Sie temporäre Ergebnismengen generieren müssen, auf die in einer SELECT -, INSERT -, UPDATE -, DELETE-oder MERGE-Anweisung zugegriffen werden kann. In gewissem Sinne ist eine CTE wie eine abgeleitete Tabelle: Sie wird nicht als Objekt gespeichert und ist nur während der Ausführung der primären Anweisung gültig. Im Gegensatz zur abgeleiteten Tabelle kann ein CTE jedoch innerhalb einer Abfrage mehrmals referenziert und selbst referenziert werden. Und das Beste ist, dass CTEs relativ einfach zu implementieren sind.,
Sie werden bemerkt haben, dass Bob AdventureWorks2008 anstelle von AdventureWorks verwendet. Wenn Sie diese Beispiele lieber für die AdventureWorks-Datenbank als für die AdventureWorks2008-Datenbank ausführen möchten, sollten Sie die Spalte BusinessEntityID in die Spalte SalesPersonID ändern.