Microsoft SQL Server
Expressions de table communes

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


Syntaxe

  • WITH cte_name [( nom_colonne_1 , nom_colonne_2 , ...)] AS ( expression_cte )

Remarques

Il est nécessaire de séparer un CTE de l'instruction précédente par un caractère point-virgule ( ; ).

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

La portée d'un CTE est un lot unique, et seulement en aval de sa définition. Un lot peut contenir plusieurs CTE et un CTE peut faire référence à un autre CTE défini précédemment dans le lot, mais un CTE peut ne pas faire référence à un autre CTE défini ultérieurement dans le lot.

Hiérarchie des employés

Configuration de la table 

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)

Expression de table commune 

;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

Sortie:

Nom complet EmpLevel ManagerName
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
Chloé Samuels 3 Žydre Klybe
George Weasley 3 Žydre Klybe
Michael Kensington 4 Sam Jackson

Trouver le neuvième salaire le plus élevé en utilisant CTE

Table d'employés: 

|  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 (expression de table commune): 

 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

Pour trouver le deuxième salaire le plus élevé, remplacez simplement N par 2. De même, pour trouver le troisième salaire le plus élevé, remplacez simplement N par 3.

Supprimer les lignes en double à l'aide de CTE

Table d'employés: 

|  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 (expression de table commune): 

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

Résultat de l'exécution: 

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

Générer une table de dates en utilisant 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)

Cet exemple renvoie une table de dates à une seule colonne, en commençant par la date spécifiée dans la variable @startdate et en renvoyant la valeur de dates @numberDays suivante.

CTE récursif

Cet exemple montre comment obtenir chaque année de cette année à 2011 (2012 - 1). 

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
mon annee
2016
2015
2014
2013
2012
2011

Vous pouvez contrôler la récursivité (débordement de la pile de réflexion dans le code) avec MAXRECURSION en tant qu'option de requête qui limitera le nombre d'appels récursifs. 

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, état 1, ligne 2L'instruction s'est terminée. La récursivité maximale 10 a été épuisée avant la fin de l'instruction.

CTE avec plusieurs déclarations AS

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

Avec les expressions de table communes, il est possible de créer plusieurs requêtes à l'aide d'instructions AS séparées par des virgules. Une requête peut alors référencer une ou toutes ces requêtes de différentes manières, même en les rejoignant.



Modified text is an extract of the original Stack Overflow Documentation
Sous licence CC BY-SA 3.0
Non affilié à Stack Overflow