Ejemplos de SELECT (Transact-SQL)
Actualizado: 14 de abril de 2006
En este tema se proporcionan ejemplos del uso de la instrucción SELECT.
A. Utilizar SELECT para recuperar filas y columnas
En el siguiente ejemplo se muestran tres fragmentos de código. En el primer ejemplo de código, se devuelven todas las filas (no se especifica la cláusula WHERE) y todas las columnas (con *) de la tabla Product de la base de datos AdventureWorks.
USE AdventureWorks ;
GO
SELECT *
FROM Production.Product
ORDER BY Name ASC ;
-- Alternate way.
USE AdventureWorks ;
GO
SELECT p.*
FROM Production.Product p
ORDER BY Name ASC ;
GO
En este ejemplo se devuelven todas las filas (no se ha especificado la cláusula WHERE) y sólo un subconjunto de las columnas (Name, ProductNumber, ListPrice) de la tabla Product de la base de datos AdventureWorks. Además, se agrega un encabezado de columna.
USE AdventureWorks ;
GO
SELECT Name, ProductNumber, ListPrice AS Price
FROM Production.Product
ORDER BY Name ASC ;
GO
En este ejemplo sólo se devuelven las filas de Product que tienen una línea de productos de R y cuyo valor correspondiente a los días para fabricar es inferior a 4.
USE AdventureWorks ;
GO
SELECT Name, ProductNumber, ListPrice AS Price
FROM Production.Product
WHERE ProductLine = 'R'
AND DaysToManufacture < 4
ORDER BY Name ASC ;
GO
B. Utilizar SELECT con encabezados de columna y cálculos
En los siguientes ejemplos se devuelven todas las filas de la tabla Product. En el primer ejemplo se devuelven las ventas totales y los descuentos de cada producto. En el segundo ejemplo se calculan los beneficios totales de cada producto.
USE AdventureWorks ;
GO
SELECT p.Name AS ProductName,
NonDiscountSales = (OrderQty * UnitPrice),
Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)
FROM Production.Product p
INNER JOIN Sales.SalesOrderDetail sod
ON p.ProductID = sod.ProductID
ORDER BY ProductName DESC ;
GO
Ésta es la consulta que calcula el beneficio de cada producto de cada pedido de venta.
USE AdventureWorks ;
GO
SELECT 'Total income is', ((OrderQty * UnitPrice) * (1.0 - UnitPriceDiscount)), ' for ',
p.Name AS ProductName
FROM Production.Product p
INNER JOIN Sales.SalesOrderDetail sod
ON p.ProductID = sod.ProductID
ORDER BY ProductName ASC ;
GO
C. Utilizar DISTINCT con SELECT
En el siguiente ejemplo se utiliza DISTINCT para evitar la recuperación de títulos duplicados.
USE AdventureWorks ;
GO
SELECT DISTINCT Title
FROM HumanResources.Employee
ORDER BY Title ;
GO
D. Crear tablas con SELECT INTO
En el primer ejemplo se crea una tabla temporal denominada #Bicycles en tempdb. Para utilizar esta tabla, haga siempre referencia a ella con el nombre exacto mostrado. Eso incluye el signo de número (#).
USE tempdb ;
IF OBJECT_ID (N'#Bicycles',N'U') IS NOT NULL
DROP TABLE #Bicycles ;
GO
USE AdventureWorks;
GO
SET NOCOUNT ON
SELECT *
INTO #Bicycles
FROM Production.Product
WHERE ProductNumber LIKE 'BK%'
SET NOCOUNT OFF
SELECT name
FROM tempdb..sysobjects
WHERE name LIKE '#Bicycles%' ;
GO
Éste es el conjunto de resultados.
name
------------------------------
#Bicycles_____________________
En el segundo ejemplo se crea la tabla permanente NewProducts.
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.NewProducts', 'U') IS NOT NULL
DROP TABLE dbo.NewProducts ;
GO
ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED ;
GO
SELECT * INTO dbo.NewProducts
FROM Production.Product
WHERE ListPrice > $25
AND ListPrice < $100
SELECT name
FROM sysobjects
WHERE name LIKE 'New%'
USE master ;
GO
ALTER DATABASE AdventureWorks SET RECOVERY FULL ;
GO
Éste es el conjunto de resultados.
name
------------------------------
NewProducts
(1 row(s) affected)
E. Utilizar subconsultas correlativas
En el siguiente ejemplo se muestran consultas que son semánticamente equivalentes y se demuestra la diferencia entre la utilización de la palabra clave EXISTS y la palabra clave IN. Ambos son ejemplos de subconsultas válidas que recuperan una instancia de cada nombre de producto cuyo modelo es un jersey de manga larga con logotipo y cuyos números de ProductModelID coinciden en las tablas Product y ProductModel.
USE AdventureWorks ;
GO
SELECT DISTINCT Name
FROM Production.Product p
WHERE EXISTS
(SELECT *
FROM Production.ProductModel pm
WHERE p.ProductModelID = pm.ProductModelID
AND pm.Name = 'Long-sleeve logo jersey') ;
GO
-- OR
USE AdventureWorks ;
GO
SELECT DISTINCT Name
FROM Production.Product
WHERE ProductModelID IN
(SELECT ProductModelID
FROM Production.ProductModel
WHERE Name = 'Long-sleeve logo jersey') ;
GO
En el siguiente ejemplo se utiliza IN en una subconsulta correlativa o repetitiva. Se trata de una consulta que depende de la consulta externa de sus valores. Se ejecuta varias veces, una vez por cada fila que pueda seleccionar la consulta externa. Esta consulta recupera una instancia del nombre y apellido de cada empleado cuya bonificación en la tabla SalesPerson sea de 5000.00 y cuyos números de identificación coincidan en las tablas Employee y SalesPerson.
USE AdventureWorks ;
GO
SELECT DISTINCT c.LastName, c.FirstName
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID WHERE 5000.00 IN
(SELECT Bonus
FROM Sales.SalesPerson sp
WHERE e.EmployeeID = sp.SalesPersonID) ;
GO
La subconsulta anterior de esta instrucción no se puede evaluar independientemente de la consulta externa. Necesita el valor Employee.EmployeeID, aunque este valor cambia a medida que el SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005) examina diferentes filas de Employee.
Una subconsulta correlativa se puede usar también en la cláusula HAVING de una consulta externa. En este ejemplo se buscan los modelos cuyo precio máximo es superior al doble de la media del modelo.
USE AdventureWorks
GO
SELECT p1.ProductModelID
FROM Production.Product p1
GROUP BY p1.ProductModelID
HAVING MAX(p1.ListPrice) >= ALL
(SELECT 2 * AVG(p2.ListPrice)
FROM Production.Product p2
WHERE p1.ProductModelID = p2.ProductModelID) ;
GO
En este ejemplo se utilizan dos subconsultas correlativas para buscar los nombres de los empleados que han vendido un producto específico.
USE AdventureWorks ;
GO
SELECT DISTINCT c.LastName, c.FirstName
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID WHERE EmployeeID IN
(SELECT SalesPersonID
FROM Sales.SalesOrderHeader
WHERE SalesOrderID IN
(SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE ProductID IN
(SELECT ProductID
FROM Production.Product p
WHERE ProductNumber = 'BK-M68B-42'))) ;
GO
F. Utilizar GROUP BY
En este ejemplo se busca el total de cada pedido de venta de la base de datos.
USE AdventureWorks ;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail sod
GROUP BY SalesOrderID
ORDER BY SalesOrderID ;
GO
Debido a la cláusula GROUP BY, sólo se devuelve una fila que contiene la suma de todas las ventas por cada pedido de venta.
G. Utilizar GROUP BY con varios grupos
En este ejemplo se busca el precio medio y la suma de las ventas anuales hasta la fecha, agrupados por Id. de producto e Id. de oferta especial.
Use AdventureWorks
SELECT ProductID, SpecialOfferID, AVG(UnitPrice) AS 'Average Price',
SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY ProductID, SpecialOfferID
ORDER BY ProductID
GO
H. Utilizar GROUP BY y WHERE
En el siguiente ejemplo se colocan los resultados en grupos después de recuperar únicamente las filas con precios superiores a $1000.
USE AdventureWorks;
GO
SELECT ProductModelID, AVG(ListPrice) AS 'Average List Price'
FROM Production.Product
WHERE ListPrice > $1000
GROUP BY ProductModelID
ORDER BY ProductModelID ;
GO
I. Utilizar GROUP BY con una expresión
En este ejemplo se agrupa por una expresión. Puede agrupar por una expresión si ésta no incluye funciones de agregado.
USE AdventureWorks ;
GO
SELECT AVG(OrderQty) AS 'Average Quantity',
NonDiscountSales = (OrderQty * UnitPrice)
FROM Sales.SalesOrderDetail sod
GROUP BY (OrderQty * UnitPrice)
ORDER BY (OrderQty * UnitPrice) DESC ;
GO
J. Comparar GROUP BY y GROUP BY ALL
En el primer ejemplo se crean grupos únicamente para los pedidos con cantidades > 10.
En el segundo ejemplo se crean grupos para todos los pedidos.
La columna que contiene el valor de agregado (el precio medio) es NULL para los grupos que carecen de filas que cumplan los criterios.
USE AdventureWorks ;
GO
SELECT ProductID, AVG(UnitPrice) AS 'Average Price'
FROM Sales.SalesOrderDetail
WHERE OrderQty > 10
GROUP BY ProductID
ORDER BY ProductID ;
GO
-- Using GROUP BY ALL
USE AdventureWorks ;
GO
SELECT ProductID, AVG(UnitPrice) AS 'Average Price'
FROM Sales.SalesOrderDetail
WHERE OrderQty > 10
GROUP BY ALL ProductID
ORDER BY ProductID ;
GO
K. Utilizar GROUP BY con ORDER BY
En este ejemplo se busca el precio medio de cada tipo de producto y se ordenan los resultados por precio medio.
USE AdventureWorks ;
GO
SELECT ProductID, AVG(UnitPrice) AS 'Average Price'
FROM Sales.SalesOrderDetail
WHERE OrderQty > 10
GROUP BY ProductID
ORDER BY AVG(UnitPrice) ;
GO
L. Utilizar la cláusula HAVING
En el primer ejemplo se muestra una cláusula HAVING con una función de agregado. Agrupa las filas de la tabla SalesOrderDetail por Id. de producto y elimina aquellos productos cuyas cantidades de pedido medias son cinco o menos. En el segundo ejemplo se muestra una cláusula HAVING sin funciones de agregado.
USE AdventureWorks ;
GO
SELECT ProductID
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID ;
GO
En esta consulta se utiliza la cláusula LIKE en la cláusula HAVING.
USE AdventureWorks ;
GO
SELECT SalesOrderID, CarrierTrackingNumber
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID, CarrierTrackingNumber
HAVING CarrierTrackingNumber LIKE '4BD%'
ORDER BY SalesOrderID ;
GO
M. Utilizar HAVING y GROUP BY
En el siguiente ejemplo se muestra el uso de las cláusulas GROUP BY, HAVING, WHERE y ORDER BY en una instrucción SELECT. Genera grupos y valores de resumen pero lo hace tras eliminar los productos cuyos precios superan los 25 $ y cuyas cantidades de pedido medias son inferiores a 5. También organiza los resultados por ProductID.
USE AdventureWorks ;
GO
SELECT ProductID
FROM Sales.SalesOrderDetail
WHERE UnitPrice < 25.00
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID ;
GO
N. Utilizar HAVING con SUM y AVG
En el siguiente ejemplo se agrupa la tabla SalesOrderDetail por Id. de producto y sólo se incluyen aquellos grupos de productos cuyos pedidos suman más de $1000000.00 y cuyas cantidades de pedido medias son inferiores a 3.
USE AdventureWorks ;
GO
SELECT ProductID, AVG(OrderQty) AS AverageQuantity, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > $1000000.00
AND AVG(OrderQty) < 3 ;
GO
Para ver los productos cuyas ventas totales son superiores a $2000000.00, utilice esta consulta:
USE AdventureWorks ;
GO
SELECT ProductID, Total = SUM(LineTotal)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > $2000000.00 ;
GO
Si desea asegurarse de que hay al menos mil quinientos elementos para los cálculos de cada producto, utilice HAVING COUNT(*) > 1500 para eliminar los productos que devuelven totales inferiores a 1500 elementos vendidos. La consulta sería la siguiente:
USE AdventureWorks ;
GO
SELECT ProductID, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING COUNT(*) > 1500 ;
GO
O. Calcular totales de grupo mediante COMPUTE BY
En este ejemplo se utilizan dos fragmentos de código para mostrar la utilización de COMPUTE BY. En el primer ejemplo de código se utiliza un elemento COMPUTE BY con una función de agregado, mientras que en el segundo se utiliza un elemento COMPUTE BY y dos funciones de agregado.
Esta consulta calcula la suma de los pedidos de aquellos productos cuyos precios son inferiores a $5.00 por cada tipo de producto.
USE AdventureWorks ;
GO
SELECT ProductID, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID, LineTotal
COMPUTE SUM(LineTotal) BY ProductID ;
GO
Esta consulta recupera el tipo de producto y la cantidad de pedidos total para aquellos productos con precios inferiores a $5.00. La cláusula COMPUTE BY utiliza dos funciones de agregado diferentes.
USE AdventureWorks ;
GO
SELECT ProductID, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID, LineTotal
COMPUTE SUM(LineTotal), MAX(LineTotal) BY ProductID ;
GO
P. Calcular valores totales mediante COMPUTE sin BY
Se puede utilizar la palabra clave COMPUTE sin BY para generar totales generales, recuentos totales, etc.
En el siguiente ejemplo se obtiene el total general de los precios y anticipos de todos los tipos de productos cuyo precio es inferior a $2.00.
USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, UnitPrice, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $2.00
COMPUTE SUM(OrderQty), SUM(LineTotal) ;
GO
Puede utilizar COMPUTE BY y COMPUTE sin BY en la misma consulta. La siguiente consulta busca la suma de las cantidades de pedido y los totales de línea por tipo de producto y, a continuación, calcula el total de pedidos y líneas.
USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, UnitPrice, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID
COMPUTE SUM(OrderQty), SUM(LineTotal) BY ProductID
COMPUTE SUM(OrderQty), SUM(LineTotal) ;
GO
Q. Calcular sumas en todas las filas
En el siguiente ejemplo se muestran sólo tres columnas de la lista de selección y se proporcionan totales basados en todas las cantidades de pedido y todos los totales de línea al final de los resultados.
USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, LineTotal
FROM Sales.SalesOrderDetail
COMPUTE SUM(OrderQty), SUM(LineTotal) ;
GO
R. Utilizar más de una cláusula COMPUTE
En el siguiente ejemplo se obtiene la suma de los precios de todos los pedidos cuyo precio es inferior a 5 $ organizada por Id. de producto y cantidad de pedido, así como la suma de los precios de todos los pedidos inferiores a 5 $ organizada únicamente por Id. de producto. Puede utilizar diferentes funciones de agregado en la misma instrucción si incluye más de una cláusula COMPUTE BY.
USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, UnitPrice, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID, OrderQty, LineTotal
COMPUTE SUM(LineTotal) BY ProductID, OrderQty
COMPUTE SUM(LineTotal) BY ProductID ;
GO
S. Comparar GROUP BY con COMPUTE
En el primer ejemplo se utiliza la cláusula COMPUTE para calcular la suma de todos los pedidos cuyo precio es inferior a $5.00 por tipo de producto. En el segundo ejemplo se genera la misma información de resumen sólo mediante GROUP BY.
USE AdventureWorks ;
GO
SELECT ProductID, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID
COMPUTE SUM(LineTotal) BY ProductID ;
GO
Ésta es la segunda consulta que utiliza GROUP BY.
USE AdventureWorks ;
GO
SELECT ProductID, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID
ORDER BY ProductID ;
GO
T. Utilizar SELECT con las cláusulas GROUP BY, COMPUTE y ORDER BY
En el siguiente ejemplo sólo se devuelven aquellos pedidos cuyo precio es inferior a 5 $ y, a continuación, se calcula la suma total de línea por producto y el total general. Todas las columnas calculadas aparecen en la lista de selección.
USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
COMPUTE SUM(SUM(LineTotal)) BY ProductID, OrderQty
COMPUTE SUM(SUM(LineTotal)) ;
GO
U. Utilizar la instrucción SELECT con CUBE
En el siguiente ejemplo se muestran dos fragmentos de código. En el primer ejemplo se devuelve un conjunto de resultados de una instrucción SELECT mediante el operador CUBE. Con el operador CUBE, la instrucción devuelve una fila adicional.
USE AdventureWorks ;
GO
SELECT ProductID, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
WITH CUBE
ORDER BY ProductID ;
GO
El valor NULL representa todos los valores de la columna ProductID. El conjunto de resultados devuelve valores para la cantidad vendida de cada producto y la cantidad total vendida de todos los productos. Al aplicar el operador CUBE o el operador ROLLUP, se devuelve el mismo resultado.
En este ejemplo se utiliza la tabla CubeExample para mostrar cómo afecta el operador CUBE al conjunto de resultados y se utiliza una función de agregado (SUM). La tabla CubeExample contiene un nombre de producto, un nombre de cliente y el número de pedidos que cada cliente ha realizado de un producto determinado.
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.CubeExample', 'U') IS NOT NULL
DROP TABLE dbo.CubeExample ;
GO
CREATE TABLE dbo.CubeExample(
ProductName VARCHAR(30) NULL,
CustomerName VARCHAR(30) NULL,
Orders INT NULL
)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Filo Mix', 'Romero y tomillo', 10)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Outback Lager', 'Wilman Kala', 10)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Filo Mix', 'Romero y tomillo', 20)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Ikura', 'Wilman Kala', 10)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Ikura', 'Romero y tomillo', 10)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Outback Lager', 'Wilman Kala', 20)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Filo Mix', 'Wilman Kala', 30)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Filo Mix', 'Eastern Connection', 40)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Outback Lager', 'Eastern Connection', 10)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Ikura', 'Wilman Kala', 40)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Ikura', 'Romero y tomillo', 10)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Filo Mix', 'Romero y tomillo', 50) ;
GO
Primero, emita una consulta típica con una cláusula GROUP BY y el conjunto de resultados.
USE AdventureWorks ;
GO
SELECT ProductName, CustomerName, SUM(Orders)
FROM CubeExample
GROUP BY ProductName, CustomerName
ORDER BY ProductName ;
GO
GROUP BY ocasiona que el conjunto de resultados forme grupos dentro de los grupos.
Éste es el conjunto de resultados.
ProductName CustomerName
------------------------------ ------------------------------ -----------
Filo Mix Eastern Connection 40
Filo Mix Romero y tomillo 80
Filo Mix Wilman Kala 30
Ikura Romero y tomillo 20
Ikura Wilman Kala 50
Outback Lager Eastern Connection 10
Outback Lager Wilman Kala 30
(7 row(s) affected)
A continuación, emita una consulta con una cláusula GROUP BY mediante el operador CUBE. El conjunto de resultados debe incluir la misma información e información de superagregado para cada una de las columnas GROUP BY.
USE AdventureWorks ;
GO
SELECT ProductName, CustomerName, SUM(Orders)
FROM CubeExample
GROUP BY ProductName, CustomerName
WITH CUBE ;
GO
El conjunto de resultados del operador CUBE incluye los valores del conjunto de resultados GROUP BY simple anterior y agrega los superagregados de cada columna de la cláusula GROUP BY. NULL representa todos los valores del conjunto desde el que se calcula el agregado.
Éste es el conjunto de resultados.
ProductName CustomerName
------------------------------ ------------------------------ -----------
Filo Mix Eastern Connection 40
Filo Mix Romero y tomillo 80
Filo Mix Wilman Kala 30
Filo Mix NULL 150
Ikura Romero y tomillo 20
Ikura Wilman Kala 50
Ikura NULL 70
Outback Lager Eastern Connection 10
Outback Lager Wilman Kala 30
Outback Lager NULL 40
NULL NULL 260
NULL Eastern Connection 50
NULL Romero y tomillo 100
NULL Wilman Kala 110
(14 row(s) affected)
La línea 4 del conjunto de resultados indica que todos los clientes han realizado un total de 150 pedidos de Filo Mix.
La línea 11 del conjunto de resultados indica que el número total de pedidos de todos los productos que han realizado todos los clientes es de 260.
Las líneas 12 a 14 del conjunto de resultados indican que el número total de pedidos de cada cliente para todos los productos es de 100, 110 y 50, respectivamente.
V. Utilizar CUBE en un conjunto de resultados con tres columnas
En el siguiente ejemplo, la instrucción SELECT devuelve el Id. de modelo del producto, el nombre del producto y la cantidad de pedidos. La cláusula GROUP BY de este ejemplo incluye las columnas ProductModelID y Name .
Al utilizar el operador CUBE, el conjunto de resultados incluye información más detallada sobre las cantidades de pedido de los productos y los modelos. NULL representa todos los valores de la columna de título.
USE AdventureWorks ;
GO
SELECT ProductModelID, p.Name AS ProductName, SUM(OrderQty)
FROM Production.Product p
INNER JOIN Sales.SalesOrderDetail sod
ON p.ProductID = sod.ProductID
GROUP BY ProductModelID, p.Name
WITH CUBE ;
GO
Al aumentar el número de columnas de la cláusula GROUP BY, se muestra por qué el operador CUBE es un operador de n dimensiones. Una cláusula GROUP BY con dos columnas devuelve tres clases más de agrupamientos cuando se utiliza el operador CUBE. El número de agrupamientos puede ser mayor de tres, en función de los distintos valores de las columnas.
El conjunto de resultados se agrupa por Id. de modelo del producto y, a continuación, por nombre de producto.
El valor NULL de la columna ProductModelID representa todos los ProductModels. El valor NULL de las columnas Name representa todos los Products. El operador CUBE devuelve los siguientes grupos de información de una instrucción SELECT:
- Cantidad de pedidos de cada modelo de un producto
- Cantidad de pedidos de cada producto
- Número total de pedidos
Cada columna a la que se hace referencia en la cláusula GROUP BY tiene una referencia cruzada con todas las demás columnas de la cláusula GROUP BY. Además, el agregado SUM se ha vuelto a aplicar. Esto da lugar a filas adicionales en el conjunto de resultados. La información que se devuelve en el conjunto de resultados crece en n dimensiones junto con el número de columnas de la cláusula GROUP BY.
[!NOTA] Asegúrese de que todas las columnas que siguen a la cláusula GROUP BY tienen relaciones significativas y reales entre sí. Por ejemplo, si utiliza Name y ProductID, el operador CUBE devuelve información irrelevante. El uso del operador CUBE en una jerarquía real, como las ventas anuales y trimestrales, da lugar a filas sin significado en el conjunto de resultados. Resulta más eficaz utilizar el operador ROLLUP.
W. Utilizar la función GROUPING con CUBE
En el siguiente ejemplo se muestra cómo la instrucción SELECT utiliza el agregado SUM, la cláusula GROUP BY y el operador CUBE. También utiliza la función GROUPING en las dos columnas que aparecen después de la cláusula GROUP BY.
USE AdventureWorks ;
GO
SELECT ProductModelID, GROUPING(ProductModelID), p.Name AS ProductName, GROUPING(p.Name), SUM(OrderQty)
FROM Production.Product p
INNER JOIN Sales.SalesOrderDetail sod
ON p.ProductID = sod.ProductID
GROUP BY ProductModelID, p.Name
WITH CUBE ;
GO
El conjunto de resultados tiene dos columnas que contienen los valores 0 y 1. Son el resultado del uso de las expresiones GROUPING(ProductModelID) y GROUPING(p.Name).
X. Utilizar el operador ROLLUP
En el siguiente ejemplo se muestran dos fragmentos de código. En el primer ejemplo se recupera el nombre de producto, el nombre de cliente y la suma de pedidos realizados y se utiliza el operador ROLLUP.
USE AdventureWorks ;
GO
SELECT ProductName, CustomerName, SUM(Orders) AS 'Sum orders'
FROM dbo.CubeExample
GROUP BY ProductName, CustomerName
WITH ROLLUP ;
GO
Éste es el conjunto de resultados.
ProductName CustomerName Sum orders
------------------------------ ------------------------------ -----------
Filo Mix Eastern Connection 40
Filo Mix Romero y tomillo 80
Filo Mix Wilman Kala 30
Filo Mix NULL 150
Ikura Romero y tomillo 20
Ikura Wilman Kala 50
Ikura NULL 70
Outback Lager Eastern Connection 10
Outback Lager Wilman Kala 30
Outback Lager NULL 40
NULL NULL 260
(11 row(s) affected)
En el segundo ejemplo se realiza una operación ROLLUP en las columnas de la organización y el departamento y se muestra el número total de empleados.
El operador ROLLUP genera un resumen de agregados. Esto resulta útil cuando se necesita información de resumen pero un CUBE completo genera datos extraños o cuando se tienen conjuntos dentro de conjuntos. Por ejemplo, los departamentos de una organización son un conjunto dentro de un conjunto.
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.Personnel', 'U') IS NOT NULL
DROP TABLE dbo.Personnel ;
GO
CREATE TABLE dbo.Personnel
(
CompanyName VARCHAR(20) NOT NULL,
Department VARCHAR(15) NOT NULL,
NumEmployees int NOT NULL
)
INSERT dbo.Personnel VALUES ('Du monde entier', 'Finance', 10)
INSERT dbo.Personnel VALUES ('Du monde entier', 'Engineering', 40)
INSERT dbo.Personnel VALUES ('Du monde entier', 'Marketing', 40)
INSERT dbo.Personnel VALUES ('Piccolo und mehr', 'Accounting', 20)
INSERT dbo.Personnel VALUES ('Piccolo und mehr', 'Personnel', 30)
INSERT dbo.Personnel VALUES ('Piccolo und mehr', 'Payroll', 40) ;
GO
En la siguiente consulta, el nombre de la organización, el departamento y la suma de todos los empleados forman parte del conjunto de resultados, además de los cálculos de ROLLUP.
USE AdventureWorks ;
GO
SELECT CompanyName, Department, SUM(NumEmployees)
FROM dbo.Personnel
GROUP BY CompanyName, Department WITH ROLLUP ;
GO
Éste es el conjunto de resultados.
CompanyName Department
-------------------- --------------- -----------
Du monde entier Engineering 40
Du monde entier Finance 10
Du monde entier Marketing 40
Du monde entier NULL 90
Piccolo und mehr Accounting 20
Piccolo und mehr Payroll 40
Piccolo und mehr Personnel 30
Piccolo und mehr NULL 90
NULL NULL 180
(9 row(s) affected)
Y. Utilizar la función GROUPING
En el siguiente ejemplo se agregan tres nuevas filas a la tabla CubeExample. Cada uno de los tres registros NULL de una o más columnas utilizados para mostrar sólo la función ROLLUP produce un valor 1 en la columna de agrupamiento. Además, este ejemplo modifica la instrucción SELECT utilizada en el ejemplo anterior.
USE AdventureWorks ;
GO
-- Add first row with a NULL customer name and 0 orders.
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Ikura', NULL, 0)
-- Add second row with a NULL product and NULL customer with real value
-- for orders.
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES (NULL, NULL, 50)
-- Add third row with a NULL product, NULL order amount, but a real
-- customer name.
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES (NULL, 'Wilman Kala', NULL)
SELECT ProductName AS Prod, CustomerName AS Cust,
SUM(Orders) AS 'Sum Orders',
GROUPING(ProductName) AS 'Group ProductName',
GROUPING(CustomerName) AS 'Group CustomerName'
FROM CubeExample
GROUP BY ProductName, CustomerName
WITH ROLLUP ;
GO
La función GROUPING sólo se puede utilizar con CUBE o ROLLUP. La función GROUPING devuelve 1 cuando una expresión se evalúa como NULL, debido a que el valor de la columna es NULL y representa el conjunto de todos los valores. La función GROUPING devuelve 0 cuando la columna correspondiente, ya sea NULL o no, no proviene de las opciones CUBE o ROLLUP como un valor de sintaxis. El valor devuelto es del tipo de datos tinyint.
Éste es el conjunto de resultados.
Prod Cust Sum Orders Group ProductName Group CustomerName
------------------------------ ------------------------------ ----------- ----------------- ------------------
NULL NULL 50 0 0
NULL Wilman Kala NULL 0 0
NULL NULL 50 0 1
Filo Mix Eastern Connection 40 0 0
Filo Mix Romero y tomillo 80 0 0
Filo Mix Wilman Kala 30 0 0
Filo Mix NULL 150 0 1
Ikura NULL 0 0 0
Ikura Romero y tomillo 20 0 0
Ikura Wilman Kala 50 0 0
Ikura NULL 70 0 1
Outback Lager Eastern Connection 10 0 0
Outback Lager Wilman Kala 30 0 0
Outback Lager NULL 40 0 1
NULL NULL 310 1 1
Warning: Null value is eliminated by an aggregate or other SET operation.
(15 row(s) affected)
Z. Utilizar SELECT con GROUP BY, una función de agregado y ROLLUP
En el siguiente ejemplo se utiliza una consulta SELECT que contiene una función de agregado y una cláusula GROUP BY.
USE AdventureWorks ;
GO
SELECT pm.Name AS ProductModel, p.Name AS ProductName, SUM(OrderQty)
FROM Production.ProductModel pm
INNER JOIN Production.Product p
ON pm.ProductModelID = p.ProductModelID
INNER JOIN Sales.SalesOrderDetail sod
ON p.ProductID = sod.ProductID
GROUP BY pm.Name, p.Name
WITH ROLLUP ;
GO
En el conjunto de resultados, NULL representa todos los valores de esa columna.
Si utiliza la instrucción SELECT sin el operador ROLLUP, la instrucción crea un solo agrupamiento. La consulta devuelve un valor de suma para cada combinación única de ProductModel, ProductModelID y ProductName:
ProductModel ProductModelID title SUM(qty)
Se puede utilizar la función GROUPING con el operador ROLLUP o con el operador CUBE. Puede aplicar esta función a una de las columnas de la lista de selección. La función devuelve 1 o 0, dependiendo de si la columna está agrupada mediante el operador ROLLUP.
a. Utilizar la sugerencia de optimizador INDEX
En el siguiente ejemplo se muestran dos formas de utilizar la sugerencia de optimizador INDEX. En el primer ejemplo se muestra cómo obligar al optimizador a que utilice un índice no agrupado para recuperar filas de una tabla, mientras que en el segundo ejemplo se obliga a realizar un recorrido de tabla mediante un índice igual a 0.
-- Use the specifically named INDEX.
USE AdventureWorks ;
GO
SELECT c.FirstName, c.LastName, e.Title
FROM HumanResources.Employee e WITH (INDEX(IX_Employee_ManagerID))
JOIN Person.Contact c on e.ContactID = c.ContactID
WHERE ManagerID = 3 ;
GO
-- Force a table scan by using INDEX = 0.
USE AdventureWorks ;
GO
SELECT c.LastName, c.FirstName, e.Title
FROM HumanResources.Employee e WITH (INDEX = 0) JOIN Person.Contact c
ON e.ContactID = c.ContactID
WHERE LastName = 'Johnson' ;
GO
b. Utilizar OPTION y las sugerencias GROUP
En el siguiente ejemplo se muestra cómo se utiliza la cláusula OPTION (GROUP) con una cláusula GROUP BY.
USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (HASH GROUP, FAST 10) ;
GO
c. Utilizar la sugerencia de consulta UNION
En el siguiente ejemplo se utiliza la sugerencia de consulta MERGE UNION.
USE AdventureWorks ;
GO
SELECT *
FROM HumanResources.Employee e1
UNION
SELECT *
FROM HumanResources.Employee e2
OPTION (MERGE UNION) ;
GO
d. Utilizar UNION simple
En el siguiente ejemplo, el conjunto de resultados incluye el contenido de las columnas ProductModelID y Name de las tablas ProductModel y Gloves.
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves ;
GO
-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4) ;
GO
-- Here is the simple union.
USE AdventureWorks ;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name ;
GO
e. Utilizar SELECT INTO con UNION
En el siguiente ejemplo, la cláusula INTO de la segunda instrucción SELECT especifica que la tabla denominada ProductResults contiene el conjunto final de resultados de la unión de las columnas designadas de las tablas ProductModel y Gloves. Tenga en cuenta que la tabla Gloves se crea en la primera instrucción SELECT.
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.ProductResults', 'U') IS NOT NULL
DROP TABLE dbo.ProductResults ;
GO
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves ;
GO
-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4) ;
GO
USE AdventureWorks ;
GO
SELECT ProductModelID, Name
INTO ProductResults
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves ;
GO
SELECT *
FROM dbo.ProductResults ;
f. Utilizar UNION de dos instrucciones SELECT con ORDER BY
El orden de algunos parámetros utilizados con la cláusula UNION es importante. En el siguiente ejemplo se muestra el uso correcto e incorrecto de UNION en dos instrucciones SELECT en las que se va a cambiar el nombre de una columna en el resultado.
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves ;
GO
-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4) ;
GO
/* INCORRECT */
USE AdventureWorks ;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
ORDER BY Name
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves ;
GO
/* CORRECT */
USE AdventureWorks ;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name ;
GO
g. Utilizar UNION de tres instrucciones SELECT para mostrar los efectos de ALL y los paréntesis
En los siguientes ejemplos se utiliza UNION para combinar los resultados de tres tablas que tienen las mismas 5 filas de datos. En el primer ejemplo se utiliza UNION ALL para mostrar los registros duplicados y se devuelven las 15 filas. En el segundo ejemplo se utiliza UNION sin ALL para eliminar las filas duplicadas de los resultados combinados de las tres instrucciones SELECT y se devuelven 5 filas.
En el tercer ejemplo se utiliza ALL con el primer UNION y los paréntesis incluyen al segundo UNION que no utiliza ALL. El segundo UNION se procesa en primer lugar porque se encuentra entre paréntesis. Devuelve 5 filas porque no se utiliza la opción ALL y se quitan los duplicados. Estas 5 filas se combinan con los resultados del primer SELECT mediante las palabras clave UNION ALL. Esto no quita los duplicados entre los dos conjuntos de 5 filas. El resultado final es de 10 filas.
USE AdventureWorks ;
GO
IF OBJECT_ID ('EmployeeOne', 'U') IS NOT NULL
DROP TABLE EmployeeOne ;
GO
IF OBJECT_ID ('EmployeeTwo', 'U') IS NOT NULL
DROP TABLE EmployeeTwo ;
GO
IF OBJECT_ID ('EmployeeThree', 'U') IS NOT NULL
DROP TABLE EmployeeThree ;
GO
SELECT c.LastName, c.FirstName, e.Title
INTO EmployeeOne
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID
WHERE ManagerID = 66 ;
GO
SELECT c.LastName, c.FirstName, e.Title
INTO EmployeeTwo
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID
WHERE ManagerID = 66 ;
GO
SELECT c.LastName, c.FirstName, e.Title
INTO EmployeeThree
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID
WHERE ManagerID = 66 ;
GO
-- Union ALL
SELECT LastName, FirstName
FROM EmployeeOne
UNION ALL
SELECT LastName, FirstName
FROM EmployeeTwo
UNION ALL
SELECT LastName, FirstName
FROM EmployeeThree ;
GO
SELECT LastName, FirstName
FROM EmployeeOne
UNION
SELECT LastName, FirstName
FROM EmployeeTwo
UNION
SELECT LastName, FirstName
FROM EmployeeThree ;
GO
SELECT LastName, FirstName
FROM EmployeeOne
UNION ALL
(
SELECT LastName, FirstName
FROM EmployeeTwo
UNION
SELECT LastName, FirstName
FROM EmployeeThree
) ;
GO
Vea también
Referencia
CREATE TRIGGER (Transact-SQL)
CREATE VIEW (Transact-SQL)
DELETE (Transact-SQL)
EXECUTE (Transact-SQL)
Expresiones (Transact-SQL)
INSERT (Transact-SQL)
LIKE (Transact-SQL)
UNION (Transact-SQL)
EXCEPT e INTERSECT (Transact-SQL)
UPDATE (Transact-SQL)
WHERE (Transact-SQL)
Otros recursos
Consultas distribuidas
Aspectos básicos de las subconsultas
Usar variables y parámetros (motor de base de datos)
Ayuda e información
Obtener ayuda sobre SQL Server 2005
Historial de cambios
| Versión | Historial |
|---|---|
14 de abril de 2006 |
|