Redgate Hub (Español)

introducida en SQL Server 2005, la expresión de tabla común (CTE) es un conjunto de resultados con nombre temporal al que puede hacer referencia dentro de una instrucción SELECT, INSERT, UPDATE o DELETE. También puede usar un CTE en una instrucción CREATE VIEW, como parte de la consulta SELECT de la vista. Además, a partir de SQL Server 2008, puede agregar un CTE a la nueva instrucción MERGE.

SQL Server admite dos tipos de CTE: recursivo y no recursivo. En este artículo, explico cómo crear ambos tipos., Los ejemplos que proporciono se basan en una instancia local de SQL Server 2008 y recuperan datos de la base de datos de muestra AdventureWorks2008.

al trabajar con expresiones de tabla comunes

Puede definir Cte añadiendo una cláusula WITH directamente antes de la instrucción SELECT, INSERT, UPDATE, DELETE o MERGE.,»>

1 2 3 4 5

] <common_table_expression>::= cte_name )] AS (cte_query)

…which can be represented like this…

As you can see, if you include more than one CTE in your WITH clause, you must separate them with commas., Además, para cada CTE, debe proporcionar un nombre, la palabra clave AS y una instrucción SELECT. También puede proporcionar nombres de columna (separados por comas), siempre que el número de nombres coincida con el número de columnas devueltas por el conjunto de resultados.

la instrucción SELECT de la consulta CTE debe cumplir los mismos requisitos que se utilizan para crear una vista. Para obtener más información sobre estos requisitos, consulte el tema «Crear vista (Transact-SQL)» en libros en línea de SQL Server. Para obtener más detalles sobre CTEs en general, consulte el tema » WITH common_table_expression (Transact-SQL).,»

Después de definir su cláusula WITH con los CTEs necesarios, puede hacer referencia a esos CTEs como lo haría con cualquier otra tabla. Sin embargo, puede hacer referencia a un CTE solo dentro del ámbito de ejecución de la instrucción que sigue inmediatamente a la cláusula WITH. Después de ejecutar su declaración, el conjunto de resultados de CTE no está disponible para otras declaraciones.

crear una expresión de tabla común no recurrente

un CTE No recurrente es aquel que no se hace referencia a sí mismo dentro del CTE. Los Cte no recursivos tienden a ser más simples que los CTE recursivos, por lo que estoy empezando con este tipo.,cteTotalSales:

después de especificar el nombre de CTE, proporciono dos nombres de columna, SalesPersonID y NetSales, que están encerrados entre paréntesis y separados por una coma. Eso significa que el conjunto de resultados devuelto por la consulta CTE debe devolver dos columnas.

a continuación, proporciono la palabra clave AS, luego un conjunto de paréntesis que encierran la consulta CTE. En este caso, la instrucción SELECT devuelve las ventas totales de cada vendedor (ventas totales agrupadas por ID de vendedor)., Como puede ver, la consulta CTE puede incluir funciones Transact-SQL, cláusulas GROUP BY o cualquier elemento que pueda incluir la instrucción SELECT en una definición de vista.

ahora puedo hacer referencia a cteTotalSales en la declaración que sigue inmediatamente. Para este ejemplo, creo una instrucción SELECT que se une a las ventas.vista de vsalesperson a cteTotalSales, basada en el ID de vendedor. Luego saco los nombres y ubicaciones de la vista y las ventas netas del CTE. La siguiente tabla muestra los resultados devueltos por esta declaración.,

Como se vio anteriormente en la sintaxis, se pueden incluir varios Cte en una cláusula WITH.,

el primer CTE-cteTotalSales-es similar al del ejemplo anterior, excepto que la cláusula WHERE ha sido calificada para incluir Ventas solo a partir de 2003. Después de definir cteTotalSales, agrego una coma y luego defino cteTargetDiff, que calcula la diferencia entre el total de ventas y la cuota de ventas.

la nueva definición de CTE especifica tres columnas para el conjunto de resultados: SalesPersonID, SalesQuota y QuotaDiff., Como era de esperar, la consulta CTE devuelve tres columnas. La primera es la identificación del vendedor. El segundo es la cuota de ventas. Sin embargo, debido a que una cuota de Ventas no está definida para algunos vendedores, uso una declaración de caso. Si el valor es null, ese valor se establece en 0, de lo contrario se utiliza el valor real de SalesQuota.

la columna final devuelta es la diferencia entre las ventas netas y la cuota de ventas. Una vez más, uso una declaración de caso. Si el valor de SalesQuota es null, entonces se utiliza el valor de NetSales, de lo contrario la cuota de ventas se resta de las ventas netas para llegar a la diferencia.,

algo interesante a tener en cuenta sobre la segunda consulta CTE es que me he unido a las ventas.Tabla del vendedor a la primera CTE-cteTotalSales – así que podría calcular la diferencia entre las ventas totales y la cuota de ventas. Siempre que defina varios CTEs en una sola cláusula WITH, puede hacer referencia a los CTEs anteriores (pero no al revés).

una vez que he definido mis CTEs, puedo hacer referencia a ellos en la primera declaración que sigue a la CTE, como se vio en el ejemplo anterior. En este caso, me uno a las ventas.,vsalesperson ver a cteTotalSales y luego unirse a cteTargetDiff, todo basado en el ID de vendedor. Mi lista de selección luego incluye columnas de las tres fuentes. La instrucción devuelve los resultados que se muestran en la siguiente tabla.

como puede ver, se proporcionan datos de ventas para todos los vendedores, incluida la ciudad en la que residen, sus ventas netas, su Cuota de ventas y la diferencia calculada entre las dos cifras. En este caso, todo el mundo supera con creces la cuota, cuando se ha definido una cuota.,

crear una expresión recursiva de tabla común

un CTE recursivo es aquel que se hace referencia a sí mismo dentro de ese CTE. El CTE recursivo es útil cuando se trabaja con datos jerárquicos porque el CTE continúa ejecutándose hasta que la consulta devuelve toda la jerarquía.

un ejemplo típico de datos jerárquicos es una tabla que incluye una lista de empleados. Para cada empleado, la tabla proporciona una referencia al gerente de esa persona. Esa referencia es en sí misma un ID de empleado dentro de la misma tabla., Puede usar un CTE recursivo para mostrar la jerarquía de los datos de los empleados, como aparecería dentro del organigrama.

tenga en cuenta que un CTE creado incorrectamente podría entrar en un bucle infinito. Para evitar esto, puede incluir la sugerencia MAXRECURSION en la cláusula OPTION de la instrucción principal SELECT, INSERT, UPDATE, DELETE o MERGE. Para obtener información sobre el uso de sugerencias de consulta, consulte el tema «sugerencias de consulta (Transact-SQL)» en SQL Server Books Online.,

para demostrar cómo funciona el CTE recursivo, utilicé las siguientes instrucciones Transact-SQL para crear y rellenar la tabla Employees en la base de datos AdventureWorks2008:

como puede darse cuenta, La base de datos AdventureWorks2008 ya incluye los Recursos Humanos.Mesa de empleados. Sin embargo, esa tabla ahora usa el tipo de datos hierarchyid para almacenar datos jerárquicos, lo que introduciría una complejidad innecesaria al intentar demostrar un CTE recursivo. Por esa razón, creé mi propia mesa., Sin embargo, si desea probar un CTE recursivo sin crear y rellenar una tabla nueva, puede utilizar la base de datos de ejemplo de AdventureWorks que se incluye con SQL Server 2005. Los Recursos Humanos.La tabla de empleados en esa base de datos almacena los datos de una manera similar a la tabla que creé anteriormente.,div>2

Como puede ver, el CTE devuelve cinco columnas: EmpID, FirstName, LastName, MgrID, y EmpLevel. La columna EmpLevel se refiere al nivel de la jerarquía en el que encajan los empleados. El nivel más alto de la jerarquía es 1, el siguiente nivel es 2, seguido por 3, y así sucesivamente.

la consulta CTE se compone de dos sentencias SELECT, conectadas con el operador UNION ALL., Una consulta CTE recursiva debe contener al menos dos miembros (sentencias), conectados por el operador UNION ALL, UNION, INTERSECT o EXCEPT. En este ejemplo, la primera instrucción SELECT es el miembro anchor, y la segunda instrucción es el miembro recursivo. Todos los miembros de anclaje deben preceder a los miembros recursivos, y solo los miembros recursivos pueden hacer referencia al CTE en sí. Además, todos los miembros deben devolver el mismo número de columnas con los tipos de datos correspondientes.

ahora veamos más de cerca las propias declaraciones., La primera instrucción, el miembro ancla, recupera el ID de empleado, el nombre, el apellido y el ID de administrador de la tabla Empleados, donde el ID de administrador es nulo. Este sería el empleado en la parte superior de la jerarquía, lo que significa que esta persona no informa a nadie. En consecuencia, el valor de ID del administrador es null. Para reflejar que esta persona está en la parte superior de la jerarquía, asigno un valor de 1 a la columna EmpLevel.

la segunda instrucción de la consulta CTE, el miembro recursivo, también recupera el ID de empleado, el nombre, el apellido y el ID de administrador de los empleados en la tabla Empleados., Sin embargo, tenga en cuenta que me uno a la tabla de empleados para el propio CTE. Además, la Unión se basa en el ID de gerente en la tabla Empleados y el ID de empleado en el CTE. Al hacer esto, el CTE recorrerá la tabla Employees hasta que devuelva toda la jerarquía.

otro elemento para notar sobre la segunda instrucción es que, para la columna EmpLevel, agrego el valor 1 al valor EmpLevel tal como aparece en el CTE. De esa manera, cada vez que la declaración recorre la jerarquía, el siguiente nivel correcto se aplica a los empleados en el nivel.,

después de definir mi cláusula WITH, creo una instrucción SELECT que recupera los datos del CTE. Tenga en cuenta, sin embargo, que para la columna Manager, recupero el nombre y apellido del empleado asociado con el ID de manager en el CTE. Esto me permite mostrar el nombre completo del gerente para cada empleado. La siguiente tabla muestra el conjunto de resultados devuelto por la instrucción SELECT y su CTE.,

como puede ver, el CTE, ya sea recursivo o no, puede ser una herramienta útil cuando necesita generar conjuntos de resultados temporales a los que se puede acceder en una instrucción SELECT, INSERT, UPDATE, DELETE o MERGE. En cierto sentido, un CTE es como una tabla derivada: no se almacena como un objeto y solo es válido durante la ejecución de la instrucción primaria. Sin embargo, a diferencia de la tabla derivada, un CTE puede ser referenciado varias veces dentro de una consulta y puede ser auto referenciado. Y lo mejor de todo, los Cte son relativamente fáciles de implementar.,

habrá notado que Bob está usando AdventureWorks2008 en lugar de AdventureWorks. Si prefiere ejecutar estos ejemplos en la base de datos AdventureWorks en lugar de en la base de datos AdventureWorks2008, debe cambiar la columna BusinessEntityID por la columna SalesPersonID.