Microsoft SQL Server
Gemeenschappelijke tabelexpressies

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


Syntaxis

  • MET cte_name [( column_name_1 , column_name_2 , ...)] AS ( cte_expression )

Opmerkingen

Het is noodzakelijk om een CTE te scheiden van de vorige instructie met een puntkomma ( ; ).

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

De reikwijdte van een CTE is een enkele batch, en alleen stroomafwaarts van de definitie. Een batch kan meerdere CTE's bevatten en een CTE kan verwijzen naar een andere CTE die eerder in de batch is gedefinieerd, maar een CTE verwijst mogelijk niet naar een andere CTE die later in de batch wordt gedefinieerd.

Werknemershiërarchie

Tabel instellen 

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)

Algemene tabeluitdrukking 

;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

Output:

Voor-en achternaam EmpLevel Manager naam
Ken Sánchez 1 nul
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

Zoek het op één na hoogste salaris met behulp van CTE

Werknemers tabel: 

|  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

Om het op één na hoogste salaris te vinden, vervang je gewoon N door 2. Op dezelfde manier, om het op twee na hoogste salaris te vinden, vervang je gewoon N door 3.

Verwijder dubbele rijen met CTE

Werknemers tabel: 

|  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

Uitvoeringsresultaat: 

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

Genereer een tabel met datums met behulp van 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 dit voorbeeld wordt een tabel met datums met één kolom geretourneerd, beginnend met de datum die is opgegeven in de variabele @startdate en de volgende datums met @numberDays worden geretourneerd.

Recursieve CTE

Dit voorbeeld laat zien hoe u elk jaar van dit jaar tot 2011 (2012 - 1) kunt komen. 

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
mijn jaar
2016
2015
2014
2013
2012
2011

U kunt de recursie beheren (denk aan stapeloverloop in code) met MAXRECURSION als een queryoptie die het aantal recursieve oproepen beperkt. 

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);

Msg 530, niveau 16, staat 1, regel 2 De verklaring is beëindigd. De maximale recursie 10 is uitgeput voordat de instructie is voltooid.

CTE met meerdere AS-verklaringen

;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
)

Met veelgebruikte tabelexpressies is het mogelijk om meerdere query's te maken met door komma's gescheiden AS-instructies. Een query kan vervolgens op veel verschillende manieren naar een of meer van die query's verwijzen, of zelfs deelnemen.



Modified text is an extract of the original Stack Overflow Documentation
Licentie onder CC BY-SA 3.0
Niet aangesloten bij Stack Overflow