Compartir a través de


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)

Ayuda e información

Obtener ayuda sobre SQL Server 2005