Usar expresiones de tabla comunes
Una expresión de tabla común (CTE) se puede considerar un conjunto de resultados temporal que se define en el ámbito de la ejecución de una instrucción única SELECT, INSERT, UPDATE, DELETE o CREATE VIEW. Una CTE es similar a una tabla derivada en que no se almacena como un objeto y dura sólo el tiempo que dura la consulta. A diferencia de una tabla derivada, una CTE puede hacer referencia a sí misma y se puede hacer referencia a ella varias veces en la misma consulta.
Una CTE se puede usar para:
- Crear una consulta recursiva. Para obtener más información, vea Consultas recursivas mediante expresiones de tabla comunes.
- Sustituir una vista cuando el uso general de una vista no sea necesario; es decir, cuando no se tenga que almacenar la definición en metadatos.
- Habilitar la agrupación por una columna que se deriva de una subselección escalar o una función que no es determinista o tiene acceso externo.
- Hacer referencia a la tabla resultante varias veces en la misma instrucción.
El uso de una CTE ofrece las ventajas de una legibilidad mejorada y la facilidad de mantenimiento de consultas complejas. La consulta se puede dividir en bloques de construcción lógicos simples e independientes. Estos bloques simples se pueden usar para crear CTE provisionales más complejas hasta que se genere el conjunto de resultados finales.
Las CTE se pueden definir en rutinas definidas por el usuario, como funciones, procedimientos almacenados, desencadenadores o vistas.
Estructura de una CTE
Una CTE está formada por un nombre de expresión que representa la CTE, una lista de columnas opcional y una consulta que define la CTE. Después de definir una CTE, se puede hacer referencia a ella como una tabla o vista en una instrucción SELECT, INSERT, UPDATE o DELETE. Una CTE también se puede usar en una instrucción CREATE VIEW como parte de su instrucción SELECT de definición.
La estructura de sintaxis básica de una CTE es:
WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
La lista de nombres de columna es opcional sólo si se proporcionan nombres distintivos en la definición de la consulta.
La instrucción para ejecutar la CTE es:
SELECT <column_list>
FROM expression_name
Ejemplo
En el siguiente ejemplo se muestran los componentes de la estructura de la CTE: nombre de expresión, lista de columnas y consulta. La expresión CTE Sales_CTE tiene tres columnas (SalesPersonID, NumberOfOrders y MaxDate) y se define como el número total de pedidos de ventas y la fecha del pedido de ventas más reciente de la tabla SalesOrderHeader para cada vendedor. Cuando se ejecuta la instrucción, se hace referencia a la CTE dos veces: una vez para devolver las columnas seleccionadas para el vendedor y otra para recuperar detalles similares para el jefe del vendedor. Los datos para el vendedor y el jefe se devuelven en una única fila.
USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN Sales_CTE AS OS
ON E.EmployeeID = OS.SalesPersonID
LEFT OUTER JOIN Sales_CTE AS OM
ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO
A continuación se muestra un conjunto de resultados parciales:
EmployeeID NumberOfOrders MaxDate ManagerID NumberOfOrders MaxDate
----------- -------------- ---------- --------- -------------- ----------
268 48 2004-06-01 273 NULL NULL
275 450 2004-06-01 268 48 2004-06-01
276 418 2004-06-01 268 48 2004-06-01
277 473 2004-06-01 268 48 2004-06-01
Vea también
Otros recursos
WITH common_table_expression (Transact-SQL)
SELECT (Transact-SQL)
INSERT (Transact-SQL)
UPDATE (Transact-SQL)
DELETE (Transact-SQL)
CREATE VIEW (Transact-SQL)