Un almacén almacena la información de sus ventas en una tabla llamada "facturas" en la cual guarda el número de factura, la fecha y el nombre del cliente y una tabla denominada "detalles" en la cual se almacenan los distintos items correspondientes a cada factura: el nombre del artículo, el precio (unitario) y la cantidad.
Se necesita una lista de todas las facturas que incluya el número, la fecha, el cliente, la cantidad de artículos comprados y el total:
select f.*,
(select count(d.numeroitem)
from Detalles as d
where f.numero=d.numerofactura) as cantidad,
(select sum(d.preciounitario*cantidad)
from Detalles as d
where f.numero=d.numerofactura) as total
from facturas as f;
El segundo "select" retorna una lista de valores de una sola columna con la cantidad de items por factura (el número de factura lo toma del "select" exterior); el tercer "select" retorna una lista de valores de una sola columna con el total por factura (el número de factura lo toma del "select" exterior); el primer "select" (externo) devuelve todos los datos de cada factura.
A este tipo de subconsulta se la denomina consulta correlacionada. La consulta interna se evalúa tantas veces como registros tiene la consulta externa, se realiza la subconsulta para cada registro de la consulta externa. El campo de la tabla dentro de la subconsulta (f.numero) se compara con el campo de la tabla externa.
En este caso, específicamente, la consulta externa pasa un valor de "numero" a la consulta interna. La consulta interna toma ese valor y determina si existe en "detalles", si existe, la consulta interna devuelve la suma. El proceso se repite para el registro de la consulta externa, la consulta externa pasa otro "numero" a la consulta interna y SQL Server repite la evaluación.
Servidor de SQL Server instalado en forma local.
Ingresemos el siguiente lote de comandos en el SQL Server Management Studio:
if object_id('detalles') is not null
drop table detalles;
if object_id('facturas') is not null
drop table facturas;
create table facturas(
numero int not null,
fecha datetime,
cliente varchar(30),
primary key(numero)
);
create table detalles(
numerofactura int not null,
numeroitem int not null,
articulo varchar(30),
precio decimal(5,2),
cantidad int,
primary key(numerofactura,numeroitem),
constraint FK_detalles_numerofactura
foreign key (numerofactura)
references facturas(numero)
on update cascade
on delete cascade,
);
go
-- Seteamos el formato de la fecha: año, mes y día:
set dateformat ymd;
insert into facturas values(1200,'2018-01-15','Juan Lopez');
insert into facturas values(1201,'2018-01-15','Luis Torres');
insert into facturas values(1202,'2018-01-15','Ana Garcia');
insert into facturas values(1300,'2018-01-20','Juan Lopez');
insert into detalles values(1200,1,'lapiz',1,100);
insert into detalles values(1200,2,'goma',0.5,150);
insert into detalles values(1201,1,'regla',1.5,80);
insert into detalles values(1201,2,'goma',0.5,200);
insert into detalles values(1201,3,'cuaderno',4,90);
insert into detalles values(1202,1,'lapiz',1,200);
insert into detalles values(1202,2,'escuadra',2,100);
insert into detalles values(1300,1,'lapiz',1,300);
-- Listado de todas las facturas que incluya el número, la fecha,
-- el cliente, la cantidad de artículos comprados y el total:
select f.*,
(select count(d.numeroitem)
from detalles as d
where f.numero=d.numerofactura) as cantidad,
(select sum(d.precio*cantidad)
from detalles as d
where f.numero=d.numerofactura) as total
from facturas as f;
La ejecución de este lote de comandos SQL genera una salida similar a:
