Construyendo CTE
En este artículo se explicará como construir una CTE, Common Table Expressión o Expresión de tabla común, sus usos son muy diversos y necesarios para simplificar consultas con referencias a varias tablas o con muchos filtros.
Para más información Ver: CTE en SQL Server
Una expresión de tabla común (CTE) es un conjunto de resultados temporal definido en la ejecución de una instrucción SELECT, INSERT, UPDATE, 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)
Una CTE es similar a una tabla derivada (Ver Subconsultas como tablas derivadas) 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.
- Sustituir la creación de una vista cuando el uso de una vista no sea necesario; es decir, cuando no se tenga que almacenar la definición de la vista en la base de datos.
- Hacer referencia a la tabla resultante varias veces en la misma instrucción.
- Las CTE tiene ventajas de legibilidad mejorada y facilidad de mantenimiento de consultas complejas.
- Las CTE se pueden definir en rutinas definidas por el usuario, como funciones, procedimientos almacenados, desencadenadores o vistas.
Ejercicios
Usando la base de datos Northwind
use Northwind
go
Ejercicio 1
Crear una CTE para los productos, incluir el proveedor y la categoria
La instrucción select es como sigue
select
P.ProductID, P.ProductName, P.UnitPrice, P.UnitsInStock,
S.CompanyName, C.CategoryName
from Products As P
join Suppliers As S on P.SupplierID = S.SupplierID
join Categories As C on P.CategoryID = C.CategoryID
go
El resultado se muestra en la siguiente imagen
Como se puede ver, los campos resultantes de la consulta son:
ProductID, ProductName, UnitPrice, UnitsInStock,
CompanyName, CategoryName
Para crear una CTE se puede especificar un nombre para cada uno de los campos de la consulta
ProductID será Codigo
ProductName será Descripción
UnitPrice será Precio
UnitsInStock será Stock
CompanyName será Proveedor
CategoryName será Categoría
Teniendo en cuenta la estructura de la CTE
WITH NombreCTE [ ( NombreColumna [,…n] ) ]
AS
( Consulta compleja )
La CTE para este ejercicio será como sigue, note que al final se debe listar los registros usando la CTE creada
With ProductosDatos
(Código, Descripción, Precio,
Stock, Proveedor, Categoría)
As
(
select
P.ProductID, P.ProductName, P.UnitPrice, P.UnitsInStock,
S.CompanyName, C.CategoryName
from Products As P
join Suppliers As S on P.SupplierID = S.SupplierID
join Categories As C on P.CategoryID = C.CategoryID
)
select * from ProductosDatos
go
El resultado se muestra en la siguiente imagen
Note que los nombres de las columnas en el resultado son los especificados en la definición de la CTE.
Ejercicio 2
Crear una CTE para las órdenes no atendidas en 1998, incluir el nombre del cliente y el empleado La instrucción select es como sigue
select
O.OrderID, O.OrderDate,
CONCAT_WS(space(1), E.LastName, E.FirstName),
C.CompanyName
from Orders As O
join Customers As C on O.CustomerId= C.CustomerID
join Employees As E on O.EmployeeID = E.EmployeeID
where O.ShippedDate is null and year(O.OrderDate) = 1998
go
El resultado se muestra en la siguiente imagen
Como se puede ver, los campos resultantes de la consulta son:
OrderID
OrderDate
CONCAT_WS(space(1), E.LastName, E.FirstName)
CompanyName
Para crear una CTE se puede especificar un nombre para cada uno de los campos de la consulta
OrderID será Nº Orden
OrderDate será Fecha
CONCAT_WS(space(1), E.LastName, E.FirstName) será Empleado
CompanyName será Cliente
La CTE para este ejercicio será como sigue
with OrdenesNoAtendidas1998
([Nº Orden], Fecha, Empleado, Cliente)
As
(
select
O.OrderID, O.OrderDate,
CONCAT_WS(space(1), E.LastName, E.FirstName),
C.CompanyName
from Orders As O
join Customers As C on O.CustomerId= C.CustomerID
join Employees As E on O.EmployeeID = E.EmployeeID
where O.ShippedDate is null and year(O.OrderDate) = 1998
)
Select * from OrdenesNoAtendidas1998
go
El resultado se muestra en la siguiente imagen
Ejercicio 3
Crear una CTE para los clientes, la cantidad de órdenes y el total de cada orden.
La cantidad de órdenes y el total de cada orden obtenerlas usando una FDU. Las FDU para este ejercicio son como sigue. (Ver Funciones definidas por el usuario)
La función definida por el usuario que devuelve la cantidad de órdenes
Create or alter function dbo.fduCantidadOrdenesCliente (@CodigoCliente nchar(5))
Returns Int
As
Begin
Declare @Cantidad Int
Set @Cantidad =
(Select Count(O.OrderID) from Orders As O where O.CustomerId = @CodigoCliente)
Return @Cantidad
End
go
La función definida por el usuario que devuelve el total de cada orden
Create or alter function dbo.fduTotalOrden (@NumeroOrden int)
Returns Numeric(9,2)
As
Begin
Declare @Total Numeric(9,2)
Set @Total =
(Select sum((D.Quantity * D.UnitPrice)*( 1- D.Discount))
from [Order Details] As D where D.OrderID= @NumeroOrden)
Return @Total
End
go
La instrucción Select es como sigue
Select
C.CustomerID, C.CompanyName, C.Country,
dbo.fduCantidadOrdenesCliente(C.CustomerID),
Sum(dbo.fduTotalOrden(O.OrderID))
from Customers As C
join Orders As O on C.CustomerID = O.CustomerID
group by
C.CustomerID, C.CompanyName, C.Country,
dbo.fduCantidadOrdenesCliente(C.CustomerID)
go
La CTE para este ejercicio es como sigue
With ClientesVentas
([Cód. Cliente],Cliente, País,
[Cantidad Órdenes], [Monto total])
As
(
Select
C.CustomerID, C.CompanyName, C.Country,
dbo.fduCantidadOrdenesCliente(C.CustomerID),
Sum(dbo.fduTotalOrden(O.OrderID))
from Customers As C
join Orders As O on C.CustomerID = O.CustomerID
group by
C.CustomerID, C.CompanyName, C.Country,
dbo.fduCantidadOrdenesCliente(C.CustomerID)
)
Select * from ClientesVentas
go
El resultado se muestra en la siguiente imagen
Ejercicio 4
Creando una CTE sin especificar el nombre de las columnas.
En este ejercicio se va a listar los productos y luego usando una CTE listar los productos con precios entre 20 y 50 ordenados por precio descendente.
With Productos2050
As
(select P.ProductID, P.ProductName, P.UnitPrice
from Products As P)
Select * from Productos2050
where UnitPrice between 20 and 50
order by UnitPrice desc
go
El resultado se muestra en la siguiente imagen
Ejercicio 5
CTE para una relación recursiva
En este ejercicio se va a listar los Empleados y sus subordinados.
with ReporteJefeSubordinado
([Cód. Jefe],[Apellido] ,[Nombre])
As
(select EmployeeID, LastName, FirstName from Employees)
Select
R.[Cód. Jefe] ,
CONCAT_WS(Space(1), R.Apellido , R.Nombre ) As ‘Jefe’,
E.EmployeeID As ‘Cód. Empleado’,
CONCAT_WS(Space(1), E.FirstName, E.LastName ) As ‘Subordinado’
from ReporteJefeSubordinado As R
join Employees As E on R.[Cód. Jefe] = E.ReportsTo
go
El resultado se muestra en la siguiente imagen
Ejercicio 6
Listar los clientes y el total de compras por año. En este ejercicio vamos a usar una CTE para realizar un Pivot. Se van a usar la FDU del ejercicio 3 que calcula el total de la orden.
With ClientesVentas
([Cód. Cliente],Cliente, Año,
[Monto total])
As
(
Select
C.CustomerID, C.CompanyName, year(O.OrderDate),
Sum(dbo.fduTotalOrden(O.OrderID))
from Customers As C
join Orders As O on C.CustomerID = O.CustomerID
group by
C.CustomerID, C.CompanyName, year(O.OrderDate)
)
Select *
from ClientesVentas As CV
pivot (sum([Monto Total])
for Año in ([1996],[1997],[1998])) Pvt
go
El resultado se muestra en la siguiente imagen
Ejercicio 7
Listar los clientes y la cantidad de órdenes por año. En este ejercicio vamos a usar una CTE para realizar un Pivot. (Ver Pivot). Se van a usar la FDU del ejercicio 3 que calcula la cantidad de órdenes
With ClientesVentas
([Cód. Cliente],Cliente, Año,
[Cantidad de órdenes])
As
(
Select
C.CustomerID, C.CompanyName, year(O.OrderDate),
Count(dbo.fduCantidadOrdenesCliente(C.CustomerID))
from Customers As C
join Orders As O on C.CustomerID = O.CustomerID
group by
C.CustomerID, C.CompanyName, year(O.OrderDate)
)
Select *
from ClientesVentas As CV
pivot (sum([Cantidad de órdenes])
for Año in ([1996],[1997],[1998])) Pvt
go
El resultado se muestra en la siguiente imagen
