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, SalesOrderID, and OrderDate) y se define como el número total de pedidos de ventas por año de cada vendedor.
USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO
A continuación se muestra un conjunto de resultados parcial:
SalesPersonID TotalSales SalesYear
------------- ----------- -----------
274 4 2001
274 20 2002
274 14 2003
274 10 2004
275 56 2001
275 139 2002
275 169 2003