WITH common_table_expression (Transact-SQL)WITH common_table_expression (Transact-SQL)

SE APLICA A: síSQL Server (a partir de 2008) síAzure SQL Database síAzure SQL Data Warehouse síAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Especifica un conjunto de resultados temporal con nombre, conocido como expresión de tabla común (CTE).Specifies a temporary named result set, known as a common table expression (CTE). Se deriva de una consulta simple y se define en el ámbito de ejecución de una sola instrucción SELECT, INSERT, UPDATE o DELETE.This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. Esta cláusula también se puede utilizar en una instrucción CREATE VIEW como parte de la instrucción SELECT que la define.This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. Una expresión de tabla común puede incluir referencias a ella misma.A common table expression can include references to itself. Esto se conoce como expresión de tabla común recursiva.This is referred to as a recursive common table expression.

Icono de vínculo de tema Convenciones de sintaxis de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxisSyntax

[ WITH <common_table_expression> [ ,...n ] ]  

<common_table_expression>::=  
    expression_name [ ( column_name [ ,...n ] ) ]  
    AS  
    ( CTE_query_definition )  

ArgumentosArguments

expression_nameexpression_name
Es un identificador válido de la expresión de tabla común.Is a valid identifier for the common table expression. expression_name debe ser diferente del nombre de cualquier otra expresión de tabla común definida en la misma cláusula WITH <common_table_expression>, pero expression_name puede coincidir con el nombre de una vista o tabla base.expression_name must be different from the name of any other common table expression defined in the same WITH <common_table_expression> clause, but expression_name can be the same as the name of a base table or view. Cualquier referencia a expression_name en la consulta usa la expresión de tabla común y no el objeto base.Any reference to expression_name in the query uses the common table expression and not the base object.

column_namecolumn_name
Especifica un nombre de columna en la expresión de tabla común.Specifies a column name in the common table expression. No se permiten nombres duplicados en una misma definición de CTE.Duplicate names within a single CTE definition are not allowed. El número de nombres de columna especificado debe coincidir con el número de columnas del conjunto de resultados de CTE_query_definition.The number of column names specified must match the number of columns in the result set of the CTE_query_definition. La lista de nombres de columna es opcional solamente si en la definición de la consulta se suministran nombres diferentes para todas las columnas resultantes.The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

CTE_query_definitionCTE_query_definition
Especifica una instrucción SELECT cuyo conjunto de resultados llena la expresión de tabla común.Specifies a SELECT statement whose result set populates the common table expression. La instrucción SELECT de CTE_query_definition debe cumplir los mismos requisitos que en la creación de una vista, excepto que una expresión CTE no puede definir otra expresión CTE.The SELECT statement for CTE_query_definition must meet the same requirements as for creating a view, except a CTE cannot define another CTE. Para más información, vea la sección Comentarios y CREATE VIEW (Transact-SQL).For more information, see the Remarks section and CREATE VIEW (Transact-SQL).

Si se definen varios parámetros CTE_query_definition, las definiciones de consulta deben combinarse mediante uno de estos operadores de conjunto: UNION ALL, UNION, EXCEPT o INTERSECT.If more than one CTE_query_definition is defined, the query definitions must be joined by one of these set operators: UNION ALL, UNION, EXCEPT, or INTERSECT.

NotasRemarks

Instrucciones para crear y utilizar expresiones de tabla comunesGuidelines for Creating and Using Common Table Expressions

Las instrucciones siguientes se aplican a expresiones de tabla comunes no recursivas.The following guidelines apply to nonrecursive common table expressions. Para obtener instrucciones que se aplican a expresiones de tabla comunes recursivas, vea "Instrucciones para definir y usar expresiones de tabla comunes recursivas" más adelante.For guidelines that apply to recursive common table expressions, see "Guidelines for Defining and Using Recursive Common Table Expressions" that follows.

  • Una expresión CTE debe ir seguida de una única instrucción SELECT, INSERT, UPDATE o DELETE que haga referencia a una parte o a la totalidad de sus columnas.A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns. Una expresión CTE también se puede especificar en una instrucción CREATE VIEW como parte de la instrucción SELECT de definición de la vista.A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.

  • Se pueden especificar varias definiciones de consulta de CTE en una CTE no recursiva.Multiple CTE query definitions can be defined in a nonrecursive CTE. Las definiciones deben combinarse mediante uno de estos operadores de conjuntos: UNION ALL, UNION, INTERSECT o EXCEPT.The definitions must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT.

  • Una expresión CTE puede hacer referencia a ella misma y a otras expresiones CTE previamente definidas en la misma cláusula WITH.A CTE can reference itself and previously defined CTEs in the same WITH clause. No se permite la referencia adelantada.Forward referencing is not allowed.

  • No se permite especificar más de una cláusula WITH en una expresión CTE.Specifying more than one WITH clause in a CTE is not allowed. Por ejemplo, si un argumento CTE_query_definition contiene una subconsulta, esta no puede contener ninguna cláusula WITH anidada que defina otra expresión CTE.For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

  • En la definición de CTE_query_definition no se pueden usar las siguientes cláusulas:The following clauses cannot be used in the CTE_query_definition:

    • ORDER BY (excepto cuando se especifica una cláusula TOP)ORDER BY (except when a TOP clause is specified)

    • INTOINTO

    • Cláusula OPTION con sugerencias de consultaOPTION clause with query hints

    • FOR BROWSEFOR BROWSE

  • Cuando se utiliza una expresión CTE en una instrucción que forma parte de un lote, la instrucción que la precede debe ir seguida de punto y coma.When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

  • Una consulta que haga referencia a una CTE se puede utilizar para definir un cursor.A query referencing a CTE can be used to define a cursor.

  • En la expresión CTE se puede hacer referencia a tablas de servidores remotos.Tables on remote servers can be referenced in the CTE.

  • Cuando se ejecuta una CTE, todas las sugerencias que hagan referencia a ella pueden entrar en conflicto con otras sugerencias detectadas cuando la CTE tiene acceso a sus tablas subyacentes, de la misma manera que las sugerencias que hacen referencia a vistas en las consultas.When executing a CTE, any hints that reference a CTE may conflict with other hints that are discovered when the CTE accesses its underlying tables, in the same manner as hints that reference views in queries. En ese caso, la consulta devuelve un error.When this occurs, the query returns an error.

Instrucciones para definir y usar expresiones de tabla comunes recursivasGuidelines for Defining and Using Recursive Common Table Expressions

Las instrucciones siguientes se aplican a la definición de una expresión de tabla común recursiva:The following guidelines apply to defining a recursive common table expression:

  • La definición de la CTE recursiva debe contener al menos dos definiciones de consulta de CTE, un miembro no recursivo y un miembro recursivo.The recursive CTE definition must contain at least two CTE query definitions, an anchor member and a recursive member. Se pueden definir varios miembros no recursivos y recursivos, aunque todas las definiciones de consultas de miembros no recursivos deben colocarse delante de la primera definición de miembro recursivo.Multiple anchor members and recursive members can be defined; however, all anchor member query definitions must be put before the first recursive member definition. Todas las definiciones de consulta de CTE son miembros no recursivos a menos que hagan referencia a la propia CTE.All CTE query definitions are anchor members unless they reference the CTE itself.

  • Los miembros no recursivos deben combinarse mediante uno de estos operadores de conjuntos: UNION ALL, UNION, INTERSECT o EXCEPT.Anchor members must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT. UNION ALL es el único operador de conjuntos permitido entre el último miembro no recursivo y el primer miembro recursivo, y si se combinan varios miembros recursivos.UNION ALL is the only set operator allowed between the last anchor member and first recursive member, and when combining multiple recursive members.

  • El número de columnas de los miembros no recursivo y recursivo debe coincidir.The number of columns in the anchor and recursive members must be the same.

  • El tipo de datos de una columna del miembro recursivo debe ser igual al tipo de datos de la columna correspondiente en el miembro no recursivo.The data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member.

  • La cláusula FROM de un miembro recursivo solo debe hacer referencia una vez a expression_name de CTE.The FROM clause of a recursive member must refer only one time to the CTE expression_name.

  • No se permiten los siguientes elementos en el parámetro CTE_query_definition de un miembro recursivo:The following items are not allowed in the CTE_query_definition of a recursive member:

    Las instrucciones siguientes se aplican al uso de una expresión de tabla común recursiva:The following guidelines apply to using a recursive common table expression:

  • Todas las columnas devueltas por la expresión CTE recursiva aceptan valores NULL independientemente de la nulabilidad de las columnas devueltas por las instrucciones SELECT participantes.All columns returned by the recursive CTE are nullable regardless of the nullability of the columns returned by the participating SELECT statements.

  • Una expresión CTE formada incorrectamente puede generar un bucle infinito.An incorrectly composed recursive CTE may cause an infinite loop. Por ejemplo, si la definición de la consulta del miembro recursivo devuelve los mismos valores para las columnas primarias y secundarias, se crea un bucle infinito.For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. Para evitar que se genere un bucle infinito, se puede limitar el número de niveles de recursividad permitidos para una instrucción determinada mediante el uso de la sugerencia MAXRECURSION y un valor de 0 a 32.767 en la cláusula OPTION de la instrucción INSERT, UPDATE, DELETE o SELECT.To prevent an infinite loop, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement. De esta manera, se puede controlar la ejecución de la instrucción hasta que se resuelva el problema de código que genera el bucle.This lets you control the execution of the statement until you resolve the code problem that is creating the loop. El valor predeterminado de todo el servidor es 100.The server-wide default is 100. Cuando se especifica 0, no se aplica ningún límite.When 0 is specified, no limit is applied. Solo se puede especificar un valor de MAXRECURSION por instrucción.Only one MAXRECURSION value can be specified per statement. Para obtener más información, vea Sugerencias de consulta (Transact-SQL).For more information, see Query Hints (Transact-SQL).

  • No se puede utilizar una vista que contenga una expresión de tabla común recursiva para actualizar datos.A view that contains a recursive common table expression cannot be used to update data.

  • Se pueden definir cursores en las consultas que utilicen expresiones CTE.Cursors may be defined on queries using CTEs. La expresión CTE es el argumento de select_statement que define el conjunto de resultados del cursor.The CTE is the select_statement argument that defines the result set of the cursor. En el caso de las CTE recursivas únicamente se permiten los cursores de solo avance rápido y estáticos (de instantánea).Only fast forward-only and static (snapshot) cursors are allowed for recursive CTEs. Si se especifica otro tipo de cursor en una CTE recursiva, el tipo de cursor se convierte a estático.If another cursor type is specified in a recursive CTE, the cursor type is converted to static.

  • En la expresión CTE se puede hacer referencia a tablas de servidores remotos.Tables on remote servers may be referenced in the CTE. Si se hace referencia al servidor remoto en el miembro recursivo de la CTE, se crea una cola para cada tabla remota de manera que se pueda tener acceso local a las tablas repetidas veces.If the remote server is referenced in the recursive member of the CTE, a spool is created for each remote table so the tables can be repeatedly accessed locally. Si es una consulta de CTE, aparecerá Index Spool/Lazy Spools en el plan de consulta y tendrá el predicado adicional WITH STACK.If it is a CTE query, Index Spool/Lazy Spools is displayed in the query plan and will have the additional WITH STACK predicate. Esta es una forma de confirmar la recursividad apropiada.This is one way to confirm proper recursion.

  • Las funciones analíticas y de agregado de la parte recursiva del CTE se aplican al conjunto para el nivel de recursividad actual y no al conjunto para el CTE.Analytic and aggregate functions in the recursive part of the CTE are applied to the set for the current recursion level and not to the set for the CTE. Las funciones como ROW_NUMBER solo funcionan sobre el subconjunto de datos que les pasa el nivel de recursividad actual y no sobre todo el conjunto de datos pasados a la parte recursiva de la CTE.Functions like ROW_NUMBER operate only on the subset of data passed to them by the current recursion level and not the entire set of data pased to the recursive part of the CTE. Para más información, vea el ejemplo K "Utilizar funciones analíticas en una CTE recursiva" más abajo.For more information, see example K. Using analytical functions in a recursive CTE that follows.

Características y limitaciones de las expresiones de tabla comunes en Almacenamiento de datos SQLSQL Data Warehouse y Almacenamiento de datos paralelosParallel Data WarehouseFeatures and Limitations of Common Table Expressions in Almacenamiento de datos SQLSQL Data Warehouse and Almacenamiento de datos paralelosParallel Data Warehouse

La implementación actual de CTE en Almacenamiento de datos SQLSQL Data Warehouse y Almacenamiento de datos paralelosParallel Data Warehouse presenta las siguientes características y limitaciones:The current implementation of CTEs in Almacenamiento de datos SQLSQL Data Warehouse and Almacenamiento de datos paralelosParallel Data Warehouse have the following features and limitations:

  • Una CTE se puede especificar en una instrucción SELECT.A CTE can be specified in a SELECT statement.

  • Una CTE se puede especificar en una instrucción CREATE VIEW.A CTE can be specified in a CREATE VIEW statement.

  • Una CTE se puede especificar en una instrucción CREATE TABLE AS SELECT.A CTE can be specified in a CREATE TABLE AS SELECT (CTAS) statement.

  • Una CTE se puede especificar en una instrucción CREATE REMOTE TABLE AS SELECT.A CTE can be specified in a CREATE REMOTE TABLE AS SELECT (CRTAS) statement.

  • Una CTE se puede especificar en una instrucción CREATE EXTERNAL TABLE AS SELECT.A CTE can be specified in a CREATE EXTERNAL TABLE AS SELECT (CETAS) statement.

  • Se puede hacer referencia a una tabla remota desde una CTE.A remote table can be referenced from a CTE.

  • Se puede hacer referencia a una tabla externa desde una CTE.An external table can be referenced from a CTE.

  • Se pueden especificar varias definiciones de consulta de CTE en una CTE.Multiple CTE query definitions can be defined in a CTE.

  • Una CTE debe ir seguida de una sola instrucción SELECT.A CTE must be followed by a single SELECT statement. No se admiten las instrucciones INSERT, UPDATE, DELETE ni MERGE.INSERT, UPDATE, DELETE, and MERGE statements are not supported.

  • No se admiten expresiones de tabla comunes que incluyan referencias a sí mismas (es decir, expresiones de tabla comunes recursivas).A common table expression that includes references to itself (a recursive common table expression) is not supported.

  • No se puede especificar más de una cláusula WITH en una expresión CTE.Specifying more than one WITH clause in a CTE is not allowed. Por ejemplo, si un argumento CTE_query_definition contiene una subconsulta, esta no puede contener ninguna cláusula WITH anidada que defina otra expresión CTE.For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

  • No se puede usar una cláusula ORDER BY en CTE_query_definition, excepto cuando se especifique una cláusula TOP.An ORDER BY clause cannot be used in the CTE_query_definition, except when a TOP clause is specified.

  • Cuando se utiliza una expresión CTE en una instrucción que forma parte de un lote, la instrucción que la precede debe ir seguida de punto y coma.When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

  • Cuando se usan en instrucciones preparadas por sp_prepare, las CTE se comportarán del mismo modo que otras instrucciones SELECT en PDW.When used in statements prepared by sp_prepare, CTEs will behave the same way as other SELECT statements in PDW. Pero si las CTE se usan como parte de una instrucción CREATE EXTERNAL TABLE AS SELECT preparada por sp_prepare, el comportamiento puede diferir de las instrucciones de SQL ServerSQL Server y de otras instrucciones de PDW, debido a la forma en que el enlace se implementa con sp_prepare.However, if CTEs are used as part of CETAS prepared by sp_prepare, the behavior can defer from SQL ServerSQL Server and other PDW statements because of the way binding is implemented for sp_prepare. Si la instrucción SELECT que hace referencia a la CTE usa una columna incorrecta que no existe en la CTE, sp_prepare pasará sin detectar el error, pero el error sí que se generará durante sp_execute.If SELECT that references CTE is using a wrong column that does not exist in CTE, the sp_prepare will pass without detecting the error, but the error will be thrown during sp_execute instead.

EjemplosExamples

A.A. Crear una expresión de tabla común simpleCreating a simple common table expression

En el siguiente ejemplo se muestra el número total de pedidos de venta por año para cada representante de ventas en Adventure Works CyclesAdventure Works Cycles.The following example shows the total number of sales orders per year for each sales representative at Adventure Works CyclesAdventure Works Cycles.


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

B.B. Usar una expresión de tabla común para limitar recuentos y promedios de informesUsing a common table expression to limit counts and report averages

En el siguiente ejemplo se muestra el número medio de pedidos de venta correspondiente a todos los años para los representantes de ventas.The following example shows the average number of sales orders for all years for the sales representatives.

WITH Sales_CTE (SalesPersonID, NumberOfOrders)  
AS  
(  
    SELECT SalesPersonID, COUNT(*)  
    FROM Sales.SalesOrderHeader  
    WHERE SalesPersonID IS NOT NULL  
    GROUP BY SalesPersonID  
)  
SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"  
FROM Sales_CTE;  
GO  

C.C. Usar varias definiciones de CTE en una sola consultaUsing multiple CTE definitions in a single query

En el ejemplo siguiente se muestra cómo definir más de una CTE en una sola consulta.The following example shows how to define more than one CTE in a single query. Observe que se usa una coma para separar las definiciones de consulta CTE.Notice that a comma is used to separate the CTE query definitions. La función FORMAT, utilizada para mostrar las cantidades de moneda en un formato de moneda, está disponible en SQL Server 2012 y versiones posteriores.The FORMAT function, used to display the monetary amounts in a currency format, is available in SQL Server 2012 and higher.


WITH Sales_CTE (SalesPersonID, TotalSales, SalesYear)  
AS  
-- Define the first CTE query.  
(  
    SELECT SalesPersonID, SUM(TotalDue) AS TotalSales, YEAR(OrderDate) AS SalesYear  
    FROM Sales.SalesOrderHeader  
    WHERE SalesPersonID IS NOT NULL  
       GROUP BY SalesPersonID, YEAR(OrderDate)  

)  
,   -- Use a comma to separate multiple CTE definitions.  

-- Define the second CTE query, which returns sales quota data by year for each sales person.  
Sales_Quota_CTE (BusinessEntityID, SalesQuota, SalesQuotaYear)  
AS  
(  
       SELECT BusinessEntityID, SUM(SalesQuota)AS SalesQuota, YEAR(QuotaDate) AS SalesQuotaYear  
       FROM Sales.SalesPersonQuotaHistory  
       GROUP BY BusinessEntityID, YEAR(QuotaDate)  
)  

-- Define the outer query by referencing columns from both CTEs.  
SELECT SalesPersonID  
  , SalesYear  
  , FORMAT(TotalSales,'C','en-us') AS TotalSales  
  , SalesQuotaYear  
  , FORMAT (SalesQuota,'C','en-us') AS SalesQuota  
  , FORMAT (TotalSales -SalesQuota, 'C','en-us') AS Amt_Above_or_Below_Quota  
FROM Sales_CTE  
JOIN Sales_Quota_CTE ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonID  
                    AND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear  
ORDER BY SalesPersonID, SalesYear;  
GO  

A continuación se muestra un conjunto parcial de resultados.Here is a partial result set.


SalesPersonID SalesYear   TotalSales    SalesQuotaYear SalesQuota  Amt_Above_or_Below_Quota  
------------- ---------   -----------   -------------- ---------- ----------------------------------   

274           2005        $32,567.92    2005           $35,000.00  ($2,432.08)  
274           2006        $406,620.07   2006           $455,000.00 ($48,379.93)  
274           2007        $515,622.91   2007           $544,000.00 ($28,377.09)  
274           2008        $281,123.55   2008           $271,000.00  $10,123.55  

D.D. Usar una expresión de tabla común recursiva para mostrar varios niveles de recursividadUsing a recursive common table expression to display multiple levels of recursion

En el ejemplo siguiente se muestra la lista jerárquica de los directivos y de los empleados que tienen a su cargo.The following example shows the hierarchical list of managers and the employees who report to them. En el ejemplo se empieza creando y rellenando la tabla dbo.MyEmployees.The example begins by creating and populating the dbo.MyEmployees table.

-- Create an Employee table.  
CREATE TABLE dbo.MyEmployees  
(  
EmployeeID smallint NOT NULL,  
FirstName nvarchar(30)  NOT NULL,  
LastName  nvarchar(40) NOT NULL,  
Title nvarchar(50) NOT NULL,  
DeptID smallint NOT NULL,  
ManagerID int NULL,  
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)   
);  
-- Populate the table with values.  
INSERT INTO dbo.MyEmployees VALUES   
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)  
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)  
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)  
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)  
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)  
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)  
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)  
,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)  
,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);  
USE AdventureWorks2012;  
GO  
WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS   
(  
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel  
    FROM dbo.MyEmployees   
    WHERE ManagerID IS NULL  
    UNION ALL  
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1  
    FROM dbo.MyEmployees AS e  
        INNER JOIN DirectReports AS d  
        ON e.ManagerID = d.EmployeeID   
)  
SELECT ManagerID, EmployeeID, Title, EmployeeLevel   
FROM DirectReports  
ORDER BY ManagerID;  
GO  

E.E. Usar una expresión de tabla común recursiva para mostrar dos niveles de recursividadUsing a recursive common table expression to display two levels of recursion

En el ejemplo siguiente se muestran los directivos y los empleados que tienen a su cargo.The following example shows managers and the employees reporting to them. El número de niveles devueltos está limitado a dos.The number of levels returned is limited to two.

USE AdventureWorks2012;  
GO  
WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS   
(  
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel  
    FROM dbo.MyEmployees   
    WHERE ManagerID IS NULL  
    UNION ALL  
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1  
    FROM dbo.MyEmployees AS e  
        INNER JOIN DirectReports AS d  
        ON e.ManagerID = d.EmployeeID   
)  
SELECT ManagerID, EmployeeID, Title, EmployeeLevel   
FROM DirectReports  
WHERE EmployeeLevel <= 2 ;  
GO  

F.F. Usar una expresión de tabla común recursiva para mostrar una lista jerárquicaUsing a recursive common table expression to display a hierarchical list

El ejemplo siguiente, que está basado en el ejemplo D, agrega los nombres del directivo y de los empleados, y sus cargos respectivos.The following example builds on Example D by adding the names of the manager and employees, and their respective titles. La jerarquía de directivos y empleados se resalta más mediante la aplicación de sangrías a cada nivel.The hierarchy of managers and employees is additionally emphasized by indenting each level.

USE AdventureWorks2012;  
GO  
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)  
AS (SELECT CONVERT(varchar(255), e.FirstName + ' ' + e.LastName),  
        e.Title,  
        e.EmployeeID,  
        1,  
        CONVERT(varchar(255), e.FirstName + ' ' + e.LastName)  
    FROM dbo.MyEmployees AS e  
    WHERE e.ManagerID IS NULL  
    UNION ALL  
    SELECT CONVERT(varchar(255), REPLICATE ('|    ' , EmployeeLevel) +  
        e.FirstName + ' ' + e.LastName),  
        e.Title,  
        e.EmployeeID,  
        EmployeeLevel + 1,  
        CONVERT (varchar(255), RTRIM(Sort) + '|    ' + FirstName + ' ' +   
                 LastName)  
    FROM dbo.MyEmployees AS e  
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID  
    )  
SELECT EmployeeID, Name, Title, EmployeeLevel  
FROM DirectReports   
ORDER BY Sort;  
GO  

G.G. Usar MAXRECURSION para cancelar una instrucciónUsing MAXRECURSION to cancel a statement

MAXRECURSION se puede utilizar para impedir que una CTE recursiva con formato incorrecto entre en un bucle infinito.MAXRECURSION can be used to prevent a poorly formed recursive CTE from entering into an infinite loop. En el ejemplo siguiente se crea un bucle infinito intencionadamente y se utiliza la sugerencia MAXRECURSION para limitar el número de niveles de recursividad a dos.The following example intentionally creates an infinite loop and uses the MAXRECURSION hint to limit the number of recursion levels to two.

USE AdventureWorks2012;  
GO  
--Creates an infinite loop  
WITH cte (EmployeeID, ManagerID, Title) as  
(  
    SELECT EmployeeID, ManagerID, Title  
    FROM dbo.MyEmployees  
    WHERE ManagerID IS NOT NULL  
  UNION ALL  
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title  
    FROM cte   
    JOIN  dbo.MyEmployees AS e   
        ON cte.ManagerID = e.EmployeeID  
)  
--Uses MAXRECURSION to limit the recursive levels to 2  
SELECT EmployeeID, ManagerID, Title  
FROM cte  
OPTION (MAXRECURSION 2);  
GO  

Después de corregir el error de código, ya no se requiere MAXRECURSION.After the coding error is corrected, MAXRECURSION is no longer required. En el siguiente ejemplo se muestra el código corregido.The following example shows the corrected code.

USE AdventureWorks2012;  
GO  
WITH cte (EmployeeID, ManagerID, Title)  
AS  
(  
    SELECT EmployeeID, ManagerID, Title  
    FROM dbo.MyEmployees  
    WHERE ManagerID IS NOT NULL  
  UNION ALL  
    SELECT  e.EmployeeID, e.ManagerID, e.Title  
    FROM dbo.MyEmployees AS e  
    JOIN cte ON e.ManagerID = cte.EmployeeID  
)  
SELECT EmployeeID, ManagerID, Title  
FROM cte;  
GO  

H.H. Usar una expresión de tabla común para recorrer selectivamente y paso a paso una relación recursiva en una instrucción SELECTUsing a common table expression to selectively step through a recursive relationship in a SELECT statement

En el ejemplo siguiente se muestra la jerarquía de ensamblados y componentes de producto necesarios para fabricar la bicicleta para ProductAssemblyID = 800.The following example shows the hierarchy of product assemblies and components that are required to build the bicycle for ProductAssemblyID = 800.

USE AdventureWorks2012;  
GO  
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS  
(  
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,  
        b.EndDate, 0 AS ComponentLevel  
    FROM Production.BillOfMaterials AS b  
    WHERE b.ProductAssemblyID = 800  
          AND b.EndDate IS NULL  
    UNION ALL  
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,  
        bom.EndDate, ComponentLevel + 1  
    FROM Production.BillOfMaterials AS bom   
        INNER JOIN Parts AS p  
        ON bom.ProductAssemblyID = p.ComponentID  
        AND bom.EndDate IS NULL  
)  
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,  
        ComponentLevel   
FROM Parts AS p  
    INNER JOIN Production.Product AS pr  
    ON p.ComponentID = pr.ProductID  
ORDER BY ComponentLevel, AssemblyID, ComponentID;  
GO  

I.I. Usar una CTE recursiva en una instrucción UPDATEUsing a recursive CTE in an UPDATE statement

En el siguiente ejemplo se actualiza el valor de PerAssemblyQty para todos los componentes que se usan para fabricar el producto 'Road-550-W Yellow, 44' (ProductAssemblyID``800).The following example updates the PerAssemblyQty value for all parts that are used to build the product 'Road-550-W Yellow, 44' (ProductAssemblyID``800). La expresión de tabla común devuelve una lista jerárquica de los elementos que se utilizan para fabricar ProductAssemblyID 800 y los componentes que se utilizan para crear esos elementos, etc.The common table expression returns a hierarchical list of parts that are used to build ProductAssemblyID 800 and the components that are used to create those parts, and so on. Solo se modifican las filas devueltas por la expresión de tabla común.Only the rows returned by the common table expression are modified.

USE AdventureWorks2012;  
GO  
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS  
(  
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,  
        b.EndDate, 0 AS ComponentLevel  
    FROM Production.BillOfMaterials AS b  
    WHERE b.ProductAssemblyID = 800  
          AND b.EndDate IS NULL  
    UNION ALL  
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,  
        bom.EndDate, ComponentLevel + 1  
    FROM Production.BillOfMaterials AS bom   
        INNER JOIN Parts AS p  
        ON bom.ProductAssemblyID = p.ComponentID  
        AND bom.EndDate IS NULL  
)  
UPDATE Production.BillOfMaterials  
SET PerAssemblyQty = c.PerAssemblyQty * 2  
FROM Production.BillOfMaterials AS c  
JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID  
WHERE d.ComponentLevel = 0;  

J.J. Usar varios miembros no recursivos y recursivosUsing multiple anchor and recursive members

En el ejemplo siguiente se utilizan varios miembros no recursivos y recursivos para devolver todos los antecesores de una persona especificada.The following example uses multiple anchor and recursive members to return all the ancestors of a specified person. Se crea una tabla y se insertan valores en ella para establecer la genealogía familiar devuelta por la CTE recursiva.A table is created and values inserted to establish the family genealogy returned by the recursive CTE.

-- Genealogy table  
IF OBJECT_ID('dbo.Person','U') IS NOT NULL DROP TABLE dbo.Person;  
GO  
CREATE TABLE dbo.Person(ID int, Name varchar(30), Mother int, Father int);  
GO  
INSERT dbo.Person   
VALUES(1, 'Sue', NULL, NULL)  
      ,(2, 'Ed', NULL, NULL)  
      ,(3, 'Emma', 1, 2)  
      ,(4, 'Jack', 1, 2)  
      ,(5, 'Jane', NULL, NULL)  
      ,(6, 'Bonnie', 5, 4)  
      ,(7, 'Bill', 5, 4);  
GO  
-- Create the recursive CTE to find all of Bonnie's ancestors.  
WITH Generation (ID) AS  
(  
-- First anchor member returns Bonnie's mother.  
    SELECT Mother   
    FROM dbo.Person  
    WHERE Name = 'Bonnie'  
UNION  
-- Second anchor member returns Bonnie's father.  
    SELECT Father   
    FROM dbo.Person  
    WHERE Name = 'Bonnie'  
UNION ALL  
-- First recursive member returns male ancestors of the previous generation.  
    SELECT Person.Father  
    FROM Generation, Person  
    WHERE Generation.ID=Person.ID  
UNION ALL  
-- Second recursive member returns female ancestors of the previous generation.  
    SELECT Person.Mother  
    FROM Generation, dbo.Person  
    WHERE Generation.ID=Person.ID  
)  
SELECT Person.ID, Person.Name, Person.Mother, Person.Father  
FROM Generation, dbo.Person  
WHERE Generation.ID = Person.ID;  
GO  

K.K. Utilizar funciones analíticas en una CTE recursivaUsing analytical functions in a recursive CTE

En el siguiente ejemplo se muestra un error que puede producirse al utilizar una función analítica o de agregado en la parte recursiva de una CTE.The following example shows a pitfall that can occur when using an analytical or aggregate function in the recursive part of a CTE.

DECLARE @t1 TABLE (itmID int, itmIDComp int);  
INSERT @t1 VALUES (1,10), (2,10);   

DECLARE @t2 TABLE (itmID int, itmIDComp int);   
INSERT @t2 VALUES (3,10), (4,10);   

WITH vw AS  
 (  
    SELECT itmIDComp, itmID  
    FROM @t1  

    UNION ALL  

    SELECT itmIDComp, itmID  
    FROM @t2  
)   
,r AS  
 (  
    SELECT t.itmID AS itmIDComp  
           , NULL AS itmID  
           ,CAST(0 AS bigint) AS N  
           ,1 AS Lvl  
    FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t (itmID)   

UNION ALL  

SELECT t.itmIDComp  
    , t.itmID  
    , ROW_NUMBER() OVER(PARTITION BY t.itmIDComp ORDER BY t.itmIDComp, t.itmID) AS N  
    , Lvl + 1  
FROM r   
    JOIN vw AS t ON t.itmID = r.itmIDComp  
)   

SELECT Lvl, N FROM r;  

Los siguientes resultados son los esperados para la consulta.The following results are the expected results for the query.

Lvl  N  
1    0  
1    0  
1    0  
1    0  
2    4  
2    3  
2    2  
2    1  

Los siguientes resultados son los resultados reales de la consulta.The following results are the actual results for the query.

Lvl  N  
1    0  
1    0  
1    0  
1    0  
2    1  
2    1  
2    1  
2    1  

N devuelve 1 para cada paso de la parte recursiva del CTE, porque solo el subconjunto de datos para ese nivel de recursividad se pasa a ROWNUMBER.N returns 1 for each pass of the recursive part of the CTE because only the subset of data for that recursion level is passed to ROWNUMBER. Por cada iteración de la parte recursiva de la consulta solo se pasa una fila a ROWNUMBER.For each of the iterations of the recursive part of the query, only one row is passed to ROWNUMBER.

Ejemplos: Almacenamiento de datos SQLSQL Data Warehouse y Almacenamiento de datos paralelosParallel Data WarehouseExamples: Almacenamiento de datos SQLSQL Data Warehouse and Almacenamiento de datos paralelosParallel Data Warehouse

L.L. Usar una expresión de tabla común en una instrucción CREATE TABLE AS SELECTUsing a common table expression within a CTAS statement

En el siguiente ejemplo se crea una tabla que contiene el número total de pedidos de venta por año de cada representante de ventas en Adventure Works CyclesAdventure Works Cycles.The following example creates a new table containing the total number of sales orders per year for each sales representative at Adventure Works CyclesAdventure Works Cycles.

-- Uses AdventureWorks  

CREATE TABLE SalesOrdersPerYear  
WITH  
(  
    DISTRIBUTION = HASH(SalesPersonID)  
)  
AS  
    -- 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  

M.M. Usar una expresión de tabla común en una instrucción CREATE EXTERNAL TABLE AS SELECTUsing a common table expression within a CETAS statement

En el siguiente ejemplo se crea una tabla externa que contiene el número total de pedidos de venta por año de cada representante de ventas en Adventure Works CyclesAdventure Works Cycles.The following example creates a new external table containing the total number of sales orders per year for each sales representative at Adventure Works CyclesAdventure Works Cycles.

-- Uses AdventureWorks  

CREATE EXTERNAL TABLE SalesOrdersPerYear  
WITH  
(  
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:5000/files/Customer',  
    FORMAT_OPTIONS ( FIELD_TERMINATOR = '|' )   
)  
AS  
    -- 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  

N.N. Uso de varias CTE separadas por comas en una instrucciónUsing multiple comma separated CTEs in a statement

En el siguiente ejemplo se muestra cómo incluir dos CTE en una misma instrucción.The following example demonstrates including two CTEs in a single statement. Las CTE no se pueden anidar (no debe haber recursión).The CTEs cannot be nested (no recursion).

WITH   
 CountDate (TotalCount, TableName) AS  
    (  
     SELECT COUNT(datekey), 'DimDate' FROM DimDate  
    ) ,  
 CountCustomer (TotalAvg, TableName) AS  
    (  
     SELECT COUNT(CustomerKey), 'DimCustomer' FROM DimCustomer  
    )  
SELECT TableName, TotalCount FROM CountDate  
UNION ALL  
SELECT TableName, TotalAvg FROM CountCustomer;  

Ver tambiénSee Also

CREATE VIEW (Transact-SQL) CREATE VIEW (Transact-SQL)
DELETE (Transact-SQL) DELETE (Transact-SQL)
EXCEPT e INTERSECT (Transact-SQL) EXCEPT and INTERSECT (Transact-SQL)
INSERT (Transact-SQL) INSERT (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
UPDATE (Transact-SQL)UPDATE (Transact-SQL)