{"id":514,"date":"2017-09-18T21:20:47","date_gmt":"2017-09-18T21:20:47","guid":{"rendered":"http:\/\/www.manualsqlserver.com\/?p=514"},"modified":"2020-05-20T04:09:53","modified_gmt":"2020-05-20T04:09:53","slug":"common-table-expressions-cte","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=514","title":{"rendered":"Common Table Expressions CTE SQL Server"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CTE_D.png\" alt=\"\" class=\"wp-image-1275\" width=\"197\" height=\"209\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Common Table Expressions &#8211; CTE<\/strong><\/h2>\n\n\n\n<p>Una expresi\u00f3n de tabla com\u00fan (CTE) es un conjunto de resultados temporal&nbsp;definido en la ejecuci\u00f3n de una instrucci\u00f3n SELECT, INSERT, UPDATE, &nbsp;DELETE o CREATE VIEW. Es como asignar un nombre a una consulta pero sin almacenarla en la base de datos como el caso de las vistas. (<a href=\"http:\/\/www.manualsqlserver.com\/?p=310\">Ver Vistas<\/a>)<\/p>\n\n\n\n<!--more-->\n\n\n\n<p>Una CTE es similar a una tabla derivada (<a href=\"https:\/\/manualsqlserver.com\/?p=664\">Ver Subconsultas como tablas derivadas<\/a>) en que no se almacena como un objeto y dura s\u00f3lo el tiempo que dura la consulta. A diferencia de una tabla derivada, una CTE puede hacer referencia a s\u00ed misma y se puede hacer referencia a ella varias veces en la misma consulta.<\/p>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p><strong>Una CTE se puede usar para:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Crear una consulta recursiva.<\/li><li>Sustituir la creaci\u00f3n de una vista cuando el uso de una vista no sea necesario; es decir, cuando no se tenga que almacenar la definici\u00f3n de la vista en la base de datos.<\/li><li>Hacer referencia a la tabla resultante varias veces en la misma instrucci\u00f3n.<\/li><li>Las CTE tiene ventajas de legibilidad mejorada y facilidad de mantenimiento de consultas complejas.<\/li><li>Las CTE se pueden definir en rutinas definidas por el usuario, como funciones, procedimientos almacenados, desencadenadores o vistas.<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Estructura de una CTE<\/strong><\/h2>\n\n\n\n<p>Las CTE tienen un nombre de expresi\u00f3n que representa la CTE, una lista de columnas opcional y una consulta que define la CTE.<br>Despu\u00e9s de definir una CTE, se puede hacer referencia a ella como una tabla o vista en una instrucci\u00f3n SELECT, INSERT, UPDATE o DELETE.<\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong>La estructura de las CTE es:<\/strong><\/p>\n\n\n\n<p>WITH NombreCTE [ ( NombreColumna [,&#8230;n] ) ]<br>AS<br>( Consulta compleja )<br>Select &lt;column_list&gt; FROM NombreCTE<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Ejercicios<\/strong><\/h2>\n\n\n\n<p><strong>Usando Northwind<\/strong><br>use Northwind<br>go<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Ejercicio 01<\/strong><\/h3>\n\n\n\n<p>Mostrar los productos, su categor\u00eda y su proveedor. Mostrar los que no est\u00e1n descontinuados&nbsp;ordenados por precio descendentemente. Para entender las relaciones entre las tablas Ver <a href=\"http:\/\/www.manualsqlserver.com\/?p=215\">Joins en SQL<\/a><\/p>\n\n\n\n<p><strong>La consulta sin CTE es como sigue<\/strong><\/p>\n\n\n\n<p>select P.ProductID, P.ProductName, P.UnitPrice, P.UnitsInStock,<br>C.CategoryName, S.CompanyName<br>from Products As P<br>join Categories As C on P.CategoryID = C.CategoryID<br>join Suppliers As S on P.SupplierID = S.SupplierID<br>where P.Discontinued = 0<br>order by P.UnitPrice desc<br>go<\/p>\n\n\n\n<p><strong>Usando CTE<\/strong><\/p>\n\n\n\n<p>with ListaProductos (Codigo, Descripci\u00f3n, Precio, Stock, Categor\u00eda, Proveedor) As<br>(select P.ProductID, P.ProductName, P.UnitPrice, P.UnitsInStock,<br>C.CategoryName, S.CompanyName<br>from Products As P<br>join Categories As C on P.CategoryID = C.CategoryID<br>join Suppliers As S on P.SupplierID = S.SupplierID<br>where P.Discontinued = 0 )<br>Select * from ListaProductos order by Precio<br>go<\/p>\n\n\n\n<p>La imagen muestra el resultado<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CTE_00.png\" alt=\"\" class=\"wp-image-1270\" width=\"844\" height=\"255\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CTE_00.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CTE_00-300x91.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CTE_00-768x233.png 768w\" sizes=\"auto, (max-width: 844px) 100vw, 844px\" \/><\/figure><\/div>\n\n\n\n<p><strong>&#8212; En la orden anterior se pueden presentar solamente algunos campos<\/strong><\/p>\n\n\n\n<p>with ListaProductos (Codigo, Descripci\u00f3n, Precio, Stock, Categor\u00eda, Proveedor) As<br>(select P.ProductID, P.ProductName, P.UnitPrice, P.UnitsInStock,<br>C.CategoryName, S.CompanyName<br>from Products As P<br>join Categories As C on P.CategoryID = C.CategoryID<br>join Suppliers As S on P.SupplierID = S.SupplierID<br>where P.Discontinued = 0 )<br>Select Codigo, Descripci\u00f3n, Categor\u00eda from ListaProductos order by Precio<br>go<\/p>\n\n\n\n<p>La imagen muestra \u00fanicamente el Codigo, la Descripci\u00f3n y la Categor\u00eda.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CTE_01.png\" alt=\"\" class=\"wp-image-1271\" width=\"734\" height=\"405\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CTE_01.png 555w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CTE_01-300x165.png 300w\" sizes=\"auto, (max-width: 734px) 100vw, 734px\" \/><\/figure><\/div>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Ejercicio 02<\/strong><\/h3>\n\n\n\n<p>Mostrar los empleados y las \u00f3rdenes generadas.<\/p>\n\n\n\n<p>Es conveniente que la consulta tenga sus alias correctos. <a href=\"http:\/\/www.manualsqlserver.com\/?p=195\">Ver Alias<\/a><\/p>\n\n\n\n<p><strong>La consulta sin CTE<\/strong><\/p>\n\n\n\n<p>select E.EmployeeID As &#8216;Codigo&#8217;, E.LastName As &#8216;Apellido&#8217; , E.FirstName As &#8216;Nombre&#8217;,<br>O.OrderID As &#8216;N\u00ba Orden&#8217;, Format(O.OrderDate,&#8217;dd\/MMM\/yyyy&#8217;) As &#8216;Fecha&#8217;<br>from Employees As E<br>join Orders As O on E.EmployeeID = O.EmployeeID<br>go<\/p>\n\n\n\n<p><strong>Asignando la CTE<\/strong><\/p>\n\n\n\n<p>with OrdenesEmpleados As<br>(select E.EmployeeID As &#8216;Codigo&#8217;, E.LastName As &#8216;Apellido&#8217; ,<br>E.FirstName As &#8216;Nombre&#8217;, O.OrderID As &#8216;N\u00ba Orden&#8217;,<br>Format(O.OrderDate,&#8217;dd\/MMM\/yyyy&#8217;) As &#8216;Fecha&#8217;<br>from Employees As E<br>join Orders As O on E.EmployeeID = O.EmployeeID)<br>select * from OrdenesEmpleados<br>go<\/p>\n\n\n\n<p><strong>&#8212; Usando la CTE con una consulta diferente, incluye el total de \u00f3rdenes. <a href=\"http:\/\/www.manualsqlserver.com\/?p=225\">Ver agrupamientos<\/a><\/strong><\/p>\n\n\n\n<p>with OrdenesEmpleados As<br>(select E.EmployeeID As &#8216;Codigo&#8217;, E.LastName As &#8216;Apellido&#8217; ,<br>E.FirstName As &#8216;Nombre&#8217;, O.OrderID As &#8216;N\u00ba Orden&#8217;,<br>Format(O.OrderDate,&#8217;dd\/MMM\/yyyy&#8217;) As &#8216;Fecha&#8217;<br>from Employees As E<br>join Orders As O on E.EmployeeID = O.EmployeeID)<\/p>\n\n\n\n<p>select Codigo, Apellido, Nombre, COUNT([N\u00ba Orden]) As &#8216;Total \u00d3rdenes&#8217;<br>from OrdenesEmpleados group by Codigo, Apellido, Nombre<br>go<\/p>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Ejercicio 03<\/strong><\/h3>\n\n\n\n<p>Mostrando los datos de una relaci\u00f3n recursiva. En la tabla Empleados (Employees) &nbsp;existe en campo ReportsTo que indica que un empleado debe reportar su trabajo al que se indica en ese campo.<\/p>\n\n\n\n<p><strong>&#8212; Visualizar los datos<\/strong><br>select E.EmployeeID, E.FirstName, E.LastName, E.ReportsTo from Employees As E<br>go<\/p>\n\n\n\n<p>En la imagen se puede mostrar que el empleado Nancy Davolio debe reportar al empleado<br>con c\u00f3digo 2 que es Andrew Fuller.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CTE_03.png\" alt=\"\" class=\"wp-image-1273\" width=\"765\" height=\"439\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CTE_03.png 537w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CTE_03-300x172.png 300w\" sizes=\"auto, (max-width: 765px) 100vw, 765px\" \/><\/figure><\/div>\n\n\n\n<p><strong>Para un listado de los empleados y sus jefes podemos usar la siguiente instrucci\u00f3n.\u00a0<\/strong><\/p>\n\n\n\n<p>with Jefe As<br>(select EmployeeID, LastName, FirstName from Employees)<br>Select J.EmployeeID As &#8216;C\u00f3d. Jefe&#8217;, [Empleado Jefe] = J.LastName + Space(1) + J.FirstName,<br>E.EmployeeID As &#8216;C\u00f3d. Empleado&#8217;, Subordinado = E.LastName + Space(1) + E.FirstName<br>from Jefe As J<br>join Employees As E on J.EmployeeID = E.ReportsTo<br>go<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CTE_04-1024x438.png\" alt=\"\" class=\"wp-image-1274\" width=\"898\" height=\"383\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CTE_04-1024x438.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CTE_04-300x128.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CTE_04-768x329.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CTE_04.png 1220w\" sizes=\"auto, (max-width: 898px) 100vw, 898px\" \/><\/figure><\/div>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Common Table Expressions &#8211; CTE Una expresi\u00f3n de tabla com\u00fan (CTE) es un conjunto de resultados temporal&nbsp;definido en la ejecuci\u00f3n de una instrucci\u00f3n SELECT, INSERT, UPDATE, &nbsp;DELETE o CREATE VIEW. Es como asignar un nombre a una consulta pero sin almacenarla en la base de datos como el caso de las vistas. (Ver Vistas)<\/p><p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=514\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1275,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,6,8],"tags":[13,24,51,52],"class_list":["post-514","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-consultasdedatos","category-funcionessql","category-programacion","tag-consulta-recursiva","tag-cte","tag-select","tag-sql","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/514","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=514"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/514\/revisions"}],"predecessor-version":[{"id":1276,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/514\/revisions\/1276"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1275"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=514"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=514"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=514"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}