Microsoft SQL Server
Allgemeine Tabellenausdrücke

.NET Framework ASP.NET C# Language Entity Framework Java Language MySQL Oracle Database PHP SQL Visual Basic .NET Language
Suche…


Syntax

  • WITH cte_name [( spaltenname_1 , spaltenname_2 , ...)] AS ( cte_expression )

Bemerkungen

Es ist notwendig, einen CTE von der vorherigen Anweisung mit einem Semikolon ( ; ) zu trennen.

dh ;WITH CommonTableName (...) SELECT ... FROM CommonTableName ...

Der Geltungsbereich eines CTE ist eine einzelne Charge und nur hinter seiner Definition. Ein Stapel kann mehrere CTEs enthalten, und ein CTE kann auf einen anderen CTE verweisen, der zuvor im Stapel definiert wurde. Ein CTE kann jedoch keinen anderen CTE angeben, der später im Stapel definiert wird.

Mitarbeiterhierarchie

Tabelleneinrichtung 

CREATE TABLE dbo.Employees
(
    EmployeeID INT NOT NULL PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    ManagerID INT NULL
)

GO

INSERT INTO Employees VALUES (101, 'Ken', 'Sánchez', NULL)
INSERT INTO Employees VALUES (102, 'Keith', 'Hall', 101)
INSERT INTO Employees VALUES (103, 'Fred', 'Bloggs', 101)
INSERT INTO Employees VALUES (104, 'Joseph', 'Walker', 102)
INSERT INTO Employees VALUES (105, 'Žydrė', 'Klybė', 101)
INSERT INTO Employees VALUES (106, 'Sam', 'Jackson', 105)
INSERT INTO Employees VALUES (107, 'Peter', 'Miller', 103)
INSERT INTO Employees VALUES (108, 'Chloe', 'Samuels', 105)
INSERT INTO Employees VALUES (109, 'George', 'Weasley', 105)
INSERT INTO Employees VALUES (110, 'Michael', 'Kensington', 106)

Allgemeiner Tabellenausdruck 

;WITH cteReports (EmpID, FirstName, LastName, SupervisorID, 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        AS e
    INNER JOIN cteReports AS r ON e.ManagerID = r.EmpID
)

SELECT
    FirstName + ' ' + LastName AS FullName,
    EmpLevel,
    (SELECT FirstName + ' ' + LastName FROM Employees WHERE EmployeeID = cteReports.SupervisorID) AS ManagerName
FROM cteReports
ORDER BY EmpLevel, SupervisorID

Ausgabe:

Vollständiger Name EmpLevel Der Name des Managers
Ken Sánchez 1 Null
Keith Hall 2 Ken Sánchez
Fred Bloggs 2 Ken Sánchez
Žydre Klybe 2 Ken Sánchez
Joseph Walker 3 Keith Hall
Peter Miller 3 Fred Bloggs
Sam Jackson 3 Žydre Klybe
Chloe Samuels 3 Žydre Klybe
George Weasley 3 Žydre Klybe
Michael Kensington 4 Sam Jackson

Finden Sie das n-te höchste Gehalt mit CTE

Mitarbeiter-Tabelle: 

|  ID  | FirstName | LastName | Gender | Salary |
+------+-----------+----------+--------+--------+
| 1    | Jahangir  | Alam     | Male   | 70000  |
| 2    | Arifur    | Rahman   | Male   | 60000  |
| 3    | Oli       | Ahammed  | Male   | 45000  |
| 4    | Sima      | Sultana  | Female | 70000  |
| 5    | Sudeepta  | Roy      | Male   | 80000  |
+------+-----------+----------+--------+--------+

CTE (Common Table Expression): 

 WITH RESULT AS
(
    SELECT SALARY,
           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK
    FROM EMPLOYEES
)
SELECT TOP 1 SALARY
FROM RESULT
WHERE DENSERANK = 1

Um das zweithöchste Gehalt zu finden, ersetzen Sie einfach N durch 2. Um das dritthöchste Gehalt zu ermitteln, ersetzen Sie einfach N durch 3.

Löschen Sie doppelte Zeilen mit CTE

Mitarbeiter-Tabelle: 

|  ID  | FirstName | LastName | Gender | Salary |
+------+-----------+----------+--------+--------+
|  1   | Mark      | Hastings | Male   | 60000  |
|  1   | Mark      | Hastings | Male   | 60000  |
|  2   | Mary      | Lambeth  | Female | 30000  |
|  2   | Mary      | Lambeth  | Female | 30000  |
|  3   | Ben       | Hoskins  | Male   | 70000  |
|  3   | Ben       | Hoskins  | Male   | 70000  |
|  3   | Ben       | Hoskins  | Male   | 70000  |
+------+-----------+----------+--------+--------+

CTE (Common Table Expression): 

WITH EmployeesCTE AS
(
   SELECT *, ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) AS RowNumber
   FROM Employees
)
DELETE FROM EmployeesCTE WHERE RowNumber > 1

Ausführungsergebnis: 

|  ID  | FirstName | LastName | Gender | Salary |
+------+-----------+----------+--------+--------+
|  1   | Mark      | Hastings | Male   | 60000  |
|  2   | Mary      | Lambeth  | Female | 30000  |
|  3   | Ben       | Hoskins  | Male   | 70000  |
+------+-----------+----------+--------+--------+

Generieren Sie eine Datumstabelle mit CTE

DECLARE @startdate CHAR(8), @numberDays TINYINT

SET @startdate = '20160101'
SET @numberDays = 10;

WITH CTE_DatesTable
AS
(
  SELECT CAST(@startdate as date) AS [date]
  UNION ALL
  SELECT DATEADD(dd, 1, [date])
  FROM CTE_DatesTable
  WHERE DATEADD(dd, 1, [date]) <= DateAdd(DAY, @numberDays-1, @startdate)
)

SELECT [date] FROM CTE_DatesTable

OPTION (MAXRECURSION 0)

In diesem Beispiel wird eine einspaltige Tabelle mit Datumsangaben zurückgegeben, die mit dem in der Variablen @startdate angegebenen Datum beginnen und den nächsten Datumswert von @numberDays zurückgeben.

Rekursiver CTE

Dieses Beispiel zeigt, wie Sie jedes Jahr von diesem Jahr bis 2011 (2012 - 1) erhalten. 

WITH yearsAgo
(
    myYear
)
AS
(
     -- Base Case: This is where the recursion starts
     SELECT DATEPART(year, GETDATE()) AS myYear

     UNION ALL  -- This MUST be UNION ALL (cannot be UNION)

     -- Recursive Section: This is what we're doing with the recursive call
     SELECT yearsAgo.myYear - 1
     FROM yearsAgo
     WHERE yearsAgo.myYear >= 2012
)
     SELECT myYear FROM yearsAgo;  -- A single SELECT, INSERT, UPDATE, or DELETE
mein Jahr
2016
2015
2014
2013
2012
2011

Sie können die Rekursion (Think Stack-Überlauf im Code) mit MAXRECURSION als Abfrageoption steuern, um die Anzahl der rekursiven Aufrufe zu begrenzen. 

WITH yearsAgo
(
    myYear
)
AS
(
     -- Base Case
     SELECT DATEPART(year , GETDATE()) AS myYear
     UNION ALL
     -- Recursive Section
     SELECT yearsAgo.myYear - 1
     FROM yearsAgo
     WHERE yearsAgo.myYear >= 2002
)
     SELECT * FROM yearsAgo
     OPTION (MAXRECURSION 10);

Meldung 530, Ebene 16, Status 1, Zeile 2Die Anweisung wurde abgebrochen. Die maximale Rekursion 10 ist vor Abschluss der Anweisung erschöpft.

CTE mit mehreren AS-Anweisungen

;WITH cte_query_1
AS
(
    SELECT *
    FROM database.table1
), 
cte_query_2 
AS
(
    SELECT *
    FROM database.table2
)
SELECT *
FROM cte_query_1
WHERE cte_query_one.fk IN
(
    SELECT PK
    FROM cte_query_2
)

Mit allgemeinen Tabellenausdrücken können mehrere Abfragen mit durch Kommas getrennten AS-Anweisungen erstellt werden. Eine Abfrage kann dann auf viele verschiedene Arten auf eine oder alle dieser Fragen verweisen und sie sogar verbinden.



Modified text is an extract of the original Stack Overflow Documentation
Lizenziert unter CC BY-SA 3.0
Nicht angeschlossen an Stack Overflow