Compartir a través de


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

Contenido nuevo:
  • Se ha incluido un ejemplo distinto sobre cómo utilizar LIKE en la cláusula HAVING.