Microsoft SQL Server
Expresiones de mesa comunes

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


Sintaxis

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

Observaciones

Es necesario separar un CTE de la declaración anterior con un carácter de punto y coma ( ; ).

es decir ;WITH CommonTableName (...) SELECT ... FROM CommonTableName ...

El alcance de un CTE es un solo lote, y solo en sentido descendente de su definición. Un lote puede contener varios CTE y un CTE puede hacer referencia a otro CTE definido anteriormente en el lote, pero un CTE no puede hacer referencia a otro CTE que se define más adelante en el lote.

Jerarquía de empleados

Configuración de la tabla 

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)

Expresión de tabla común 

;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

Salida:

Nombre completo EmpLevel Nombre del gerente
Ken Sánchez 1 nulo
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

Encuentra el enésimo salario más alto usando CTE

Tabla de empleados: 

|  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 (expresión de tabla común): 

 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

Para encontrar el segundo salario más alto, simplemente reemplace N por 2. Del mismo modo, para encontrar el tercer salario más alto, simplemente reemplace N por 3.

Eliminar filas duplicadas utilizando CTE

Tabla de empleados: 

|  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 (expresión de tabla común): 

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

Resultado de la ejecución: 

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

Generar una tabla de fechas utilizando 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)

Este ejemplo devuelve una tabla de fechas de una sola columna, comenzando con la fecha especificada en la variable @startdate y devolviendo el siguiente @numberDays valor de fechas.

CTE recursivo

Este ejemplo muestra cómo obtener cada año desde este año hasta 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
mi año
2016
2015
2014
2013
2012
2011

Puede controlar la recursión (piense el desbordamiento de pila en el código) con MAXRECURSION como una opción de consulta que limitará el número de llamadas recursivas. 

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

Mensaje 530, nivel 16, estado 1, línea 2 La declaración terminó. La recursión máxima 10 se ha agotado antes de completar la declaración.

CTE con múltiples declaraciones 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
)

Con las expresiones de tabla comunes, es posible crear múltiples consultas utilizando instrucciones AS separadas por comas. Una consulta puede hacer referencia a cualquiera o todas esas consultas de muchas maneras diferentes, incluso uniéndolas.



Modified text is an extract of the original Stack Overflow Documentation
Licenciado bajo CC BY-SA 3.0
No afiliado a Stack Overflow