Condividi tramite


Esempi di istruzioni SELECT (Transact-SQL)

Data aggiornamento: 14 aprile 2006

In questo argomento sono inclusi alcuni esempi relativi all'utilizzo dell'istruzione SELECT.

A. Utilizzo dell'istruzione SELECT per il recupero di righe e colonne

Nell'esempio seguente vengono illustrati tre blocchi di codice. Nel primo esempio di codice vengono restituite tutte le righe (clausola WHERE omessa) e tutte le colonne (utilizzando *) della tabella Product del database 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

Nell'esempio seguente vengono restituite tutte le righe (clausola WHERE omessa) e solo un subset delle colonne (Name, ProductNumber, ListPrice) della tabella Product del database AdventureWorks. Viene aggiunta, inoltre, un'intestazione di colonna.

USE AdventureWorks ;
GO
SELECT Name, ProductNumber, ListPrice AS Price
FROM Production.Product 
ORDER BY Name ASC ;
GO

Nell'esempio seguente vengono restituite solo le righe della tabella Product caratterizzate da una riga di prodotto R e da un numero di giorni per l'invio in produzione minore di 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. Utilizzo dell'istruzione SELECT con intestazioni e calcoli di colonna

Nell'esempio seguente vengono restituite tutte le righe della tabella Product. Nel primo esempio vengono restituite le vendite totali e gli sconti per ogni prodotto. Nel secondo esempio vengono calcolati i ricavi totali per ogni prodotto.

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

Questa è la query che calcola il ricavo per ogni prodotto di ogni ordine di vendita.

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. Utilizzo della clausola DISTINCT con l'istruzione SELECT

Nell'esempio seguente viene utilizzata la clausola DISTINCT per evitare il recupero di titoli duplicati.

USE AdventureWorks ;
GO
SELECT DISTINCT Title
FROM HumanResources.Employee
ORDER BY Title ;
GO

D. Creazione di tabelle con l'istruzione SELECT INTO

Nell'esempio seguente viene creata una tabella temporanea denominata #Bicycles in tempdb. Per utilizzare la tabella, è necessario farvi sempre riferimento specificando il nome esattamente come riportato, incluso il simbolo di cancelletto (#).

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

Set di risultati:

name                          
------------------------------
#Bicycles_____________________

Nel secondo esempio viene creata la tabella 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

Set di risultati:

name                          
------------------------------
NewProducts                   
(1 row(s) affected)

E. Utilizzo di subquery correlate

Nell'esempio seguente vengono illustrate query semanticamente equivalenti e viene evidenziata la differenza tra la parola chiave EXISTS e la parola chiave IN. Entrambi sono esempi di subquery valide che recuperano un'istanza del nome di ogni prodotto del modello "Long-sleeve logo jersey" e con valori ProductModelID uguali nelle tabelle Product e 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

Nell'esempio seguente viene utilizzata la parola chiave IN in una subquery correlata o ripetuta. È una query che dipende dalla query esterna. La query viene eseguita ripetutamente, una volta per ogni riga che può essere selezionata dalla query esterna. Questa query recupera un'istanza del nome e del cognome di ogni dipendente il cui bonus nella tabella SalesPerson corrisponde a 5000.00 e con numero di identificazione uguale nelle tabelle Employee e 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 subquery precedente non può essere valutata indipendentemente dalla query esterna. Richiede un valore per Employee.EmployeeID, ma questo valore cambia quando Motore di database di SQL Server 2005 esamina righe diverse della tabella Employee.

È inoltre possibile inserire una subquery correlata nella clausola HAVING della query esterna. Nell'esempio vengono trovati i modelli il cui prezzo massimo è più del doppio del prezzo medio per il modello.

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

In questo esempio vengono utilizzate due subquery correlate per trovare i nomi dei dipendenti che hanno venduto un determinato prodotto.

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. Utilizzo della clausola GROUP BY

Nell'esempio seguente viene trovato il totale di ogni ordine di vendita nel database.

USE AdventureWorks ;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail sod
GROUP BY SalesOrderID
ORDER BY SalesOrderID ;
GO

La presenza della clausola GROUP BY comporta la restituzione di una sola riga contenente il totale di tutte le vendite per ogni ordine di vendita.

G. Utilizzo della clausola GROUP BY con più gruppi

Nell'esempio seguente vengono individuati il prezzo medio e il totale delle vendite per l'anno in corso raggruppati per ID del prodotto e ID dell'offerta speciale.

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. Utilizzo delle clausole GROUP BY e WHERE

Nell'esempio seguente i risultati vengono suddivisi in gruppi dopo che sono state recuperate le righe con prezzi maggiori di $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. Utilizzo della clausola GROUP BY con un'espressione

Nell'esempio seguente vengono creati gruppi in base a un'espressione. È possibile creare gruppi in base a un'espressione se tale espressione non include funzioni di aggregazione.

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. Confronto tra la clausola GROUP BY e la clausola GROUP BY ALL

Nel primo esempio vengono creati gruppi solo per gli ordini con quantitativi > 10.

Nel secondo esempio vengono creati gruppi per tutti gli ordini.

Per i gruppi che non includono alcuna riga che soddisfa le condizioni specificate, la colonna del valore aggregato (il prezzo medio) è NULL.

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. Utilizzo della clausola GROUP BY con la clausola ORDER BY

Nell'esempio seguente viene individuato il prezzo medio di ogni tipo di prodotto e i risultati vengono ordinati in base al prezzo 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. Utilizzo della clausola HAVING

Nel primo esempio viene illustrata la clausola HAVING con una funzione di aggregazione. Le righe della tabella SalesOrderDetail vengono raggruppate per ID di prodotto e vengono eliminati i prodotti con ordini con quantitativo medio minore o uguale a cinque. Nel secondo esempio viene illustrata una clausola HAVING senza funzioni di aggregazione.

USE AdventureWorks ;
GO
SELECT ProductID 
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID ;
GO

Questa query utilizza la clausola LIKE all'interno della clausola HAVING.

USE AdventureWorks ;
GO
SELECT SalesOrderID, CarrierTrackingNumber 
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID, CarrierTrackingNumber
HAVING CarrierTrackingNumber LIKE '4BD%'
ORDER BY SalesOrderID ;
GO

M. Utilizzo delle clausole HAVING e GROUP BY

Nell'esempio seguente viene illustrato l'utilizzo delle clausole GROUP BY, HAVING, WHERE e ORDER BY in un'istruzione SELECT. Vengono creati gruppi e valori di riepilogo, ma solo dopo l'eliminazione dei prodotti con prezzo maggiore di $25 e quantitativo medio minore di 5. I risultati vengono organizzati in base a 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. Utilizzo della clausola HAVING con le funzioni SUM e AVG

Nell'esempio seguente il contenuto della tabella SalesOrderDetail viene raggruppato in base all'ID prodotto e vengono inclusi solo i gruppi di prodotti con ordini che ammontano a più di $1000000.00 e con quantitativo medio minore di 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

Per visualizzare i prodotti con vendite totali maggiori di $2000000.00, utilizzare la query seguente:

USE AdventureWorks ;
GO
SELECT ProductID, Total = SUM(LineTotal)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > $2000000.00 ;
GO

Se si desidera che nei calcoli siano inclusi solo i prodotti per cui sono stati venduti almeno 1500 articoli, utilizzare la clausola HAVING COUNT(*) > 1500 per eliminare i prodotti per cui sono stati venduti meno di 1500 articoli. La query è la seguente:

USE AdventureWorks ;
GO
SELECT ProductID, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING COUNT(*) > 1500 ;
GO

O. Calcolo dei totali di gruppo con la clausola COMPUTE BY

Nell'esempio seguente vengono utilizzati due esempi di codice per illustrare l'utilizzo della clausola COMPUTE BY. Nel primo esempio viene utilizzata una clausola COMPUTE BY con una funzione di aggregazione, mentre nel secondo vengono utilizzate una clausola COMPUTE BY e due funzioni di aggregazione.

Per ogni tipo di prodotto la query calcola la somma degli ordini per i prodotti con prezzo minore di $5.00.

USE AdventureWorks ;
GO
SELECT ProductID, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID, LineTotal
COMPUTE SUM(LineTotal) BY ProductID ;
GO

La query recupera il tipo di prodotto e il totale degli ordini per i prodotti con prezzo unitario minore di $5.00. Nella clausola COMPUTE BY vengono utilizzate due diverse funzioni di aggregazione.

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. Calcolo di valori complessivi con COMPUTE senza BY

Se si utilizza la parola chiave COMPUTE senza specificare BY, è possibile ottenere totali complessivi, conteggi complessivi e così via.

Nell'esempio seguente viene calcolato il totale complessivo dei prezzi e degli anticipi per tutti i tipi di prodotto con prezzo minore di $2.00.

USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, UnitPrice, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $2.00
COMPUTE SUM(OrderQty), SUM(LineTotal) ;
GO

Nella stessa query è possibile utilizzare COMPUTE BY e COMPUTE senza la parola chiave BY. La query seguente calcola la somma dei quantitativi ordinati e dei totali di riga in base al tipo di prodotto, quindi calcola il totale complessivo dei quantitativi ordinati e i totali di riga.

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. Calcolo di somme calcolate in tutte le righe

Nell'esempio seguente vengono illustrate tre colonne dell'elenco di selezione. Vengono inoltre visualizzati i totali basati su tutti i quantitativi ordinati e tutti i totali di riga alla fine dei risultati.

USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, LineTotal
FROM Sales.SalesOrderDetail
COMPUTE SUM(OrderQty), SUM(LineTotal) ;
GO

R. Utilizzo di più clausole COMPUTE

Nell'esempio seguente viene calcolata la somma dei prezzi di tutti gli ordini con prezzo unitario minore di $5 organizzati per ID prodotto e quantitativo ordinato, nonché la somma dei prezzi di tutti gli ordini minori di $5 organizzati solo per ID prodotto. È possibile utilizzare diverse funzioni di aggregazione nella stessa istruzione includendo più clausole 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. Confronto della clausola GROUP BY con la clausola COMPUTE

Nel primo esempio viene utilizzata la clausola COMPUTE per calcolare la somma di tutti gli ordini con prezzo unitario minore di $5.00 per tipo di prodotto. Nel secondo esempio vengono restituite le stesse informazioni di riepilogo utilizzando solo la clausola GROUP BY.

USE AdventureWorks ;
GO
SELECT ProductID, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID
COMPUTE SUM(LineTotal) BY ProductID ;
GO

Questa è la seconda query che utilizza la clausola 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. Utilizzo dell'istruzione SELECT con le clausole GROUP BY, COMPUTE e ORDER BY

Nell'esempio seguente vengono restituiti solo gli ordini con prezzo unitario minore di $5, quindi viene calcolata la somma dei totali di riga per prodotto e il totale complessivo. Tutte le colonne calcolate sono incluse nell'elenco di selezione.

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. Utilizzo dell'istruzione SELECT con l'operatore CUBE

Nell'esempio seguente vengono riportati due esempi di codice. Il primo esempio restituisce il set di risultati di un'istruzione SELECT che utilizza l'operatore CUBE. Tramite l'operatore CUBE, l'istruzione restituisce una riga aggiuntiva.

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

NULL rappresenta tutti i valori della colonna ProductID. Il set di risultati restituisce valori relativi alla quantità venduta per ogni prodotto e a libro e alla quantità totale venduta per tutti i prodotti. Vengono restituiti gli stessi risultati sia con l'operatore CUBE che con l'operatore ROLLUP.

Nell'esempio seguente viene utilizzata la tabella CubeExample per illustrare come l'operatore CUBE influisca sul set di risultati e utilizzi una funzione di aggregazione (SUM). La tabella CubeExample include un nome di prodotto, un nome di cliente e il numero di ordini presentati da ogni cliente per un particolare prodotto.

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

Eseguire innanzitutto una query tipica con una clausola GROUP BY e il set di risultati.

USE AdventureWorks ;
GO
SELECT ProductName, CustomerName, SUM(Orders)
FROM CubeExample
GROUP BY ProductName, CustomerName
ORDER BY ProductName ;
GO

In base alla clausola GROUP BY il set di risultati crea gruppi all'interno di gruppi.

Set di risultati:

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)

Eseguire quindi una query con una clausola GROUP BY specificando l'operatore CUBE. Il set di risultati deve includere le stesse informazioni e informazioni di sovraggregazione per ogni colonna della clausola GROUP BY.

USE AdventureWorks ;
GO
SELECT ProductName, CustomerName, SUM(Orders)
FROM CubeExample
GROUP BY ProductName, CustomerName
WITH CUBE ;
GO

Il set di risultati per l'operatore CUBE contiene i valori del semplice set di risultati GROUP BY precedente, più i valori di sovraggregazione per ogni colonna della clausola GROUP BY. NULL rappresenta tutti i valori del set di risultati in base a cui viene calcolata la funzione di aggregazione.

Set di risultati:

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 riga 4 del set di risultati indica che tutti i clienti hanno presentato in totale 150 ordini di Filo Mix.

La riga 11 del set di risultati indica che tutti i clienti hanno ordinato in totale 260 prodotti.

Le righe da 12 a 14 del set di risultati indicano che ogni cliente ha ordinato in totale 100, 110 e 50 prodotti rispettivamente.

V. Utilizzo dell'operatore CUBE in un set di risultati con tre colonne

Nell'esempio seguente l'istruzione SELECT restituisce l'ID del modello del prodotto, il nome del prodotto e il numero di ordini. La clausola GROUP BY include le colonne ProductModelID e Name .

Grazie all'utilizzo dell'operatore CUBE il set di risultati contiene informazioni più dettagliate sul numero di ordini per prodotti e modelli di prodotto. NULL rappresenta tutti i valori nella colonna dei titoli.

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

Se si aumenta il numero di colonne nella clausola GROUP BY, risulta chiaro perché l'operatore CUBE sia un operatore a n dimensioni. Una clausola GROUP BY con due colonne restituisce altri tre tipi di raggruppamenti quando viene utilizzato l'operatore CUBE. Il numero di raggruppamenti può essere superiore a tre a seconda dei valori nelle colonne.

Il set di risultati viene raggruppato in base all'ID del modello, quindi in base al nome del prodotto.

NULL nella colonna ProductModelID rappresenta tutti i ProductModels. NULL nelle colonne Name rappresenta tutti i Products. L'operatore CUBE restituisce i gruppi di informazioni seguenti da un'istruzione SELECT:

  • Quantità di ordini per ogni modello di prodotto
  • Quantità di ordini per ogni prodotto
  • Numero totale di ordini

Per ogni colonna a cui viene fatto riferimento nella clausola GROUP BY è stato eseguito un riferimento incrociato con tutte le altre colonne nella clausola GROUP BY, quindi è stata rieseguita la funzione di aggregazione SUM. Questo genera righe aggiuntive nel set di risultati. Le informazioni restituite nel set di risultati aumentano di ndimensioni con l'aumentare del numero di colonne nella clausola GROUP BY.

[!NOTA] Assicurarsi che le colonne specificate nella clausola GROUP BY siano collegate da relazioni significative e reali. Se, ad esempio, si utilizzano Name e ProductID, l'operatore CUBE restituisce informazioni irrilevanti. Utilizzando l'operatore CUBE in una gerarchia reale, come le vendite annuali e trimestrali, nel set di risultati vengono restituite righe non significative. Risulta più efficiente utilizzare l'operatore ROLLUP.

W. Utilizzo della funzione GROUPING con l'operatore CUBE

Nell'esempio seguente viene illustrato l'utilizzo da parte dell'istruzione SELECT della funzione di aggregazione SUM, della clausola GROUP BY e dell'operatore CUBE. Viene illustrato inoltre l'utilizzo della funzione GROUPING sulle due colonne specificate con la clausola 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

Il set di risultati include due colonne con i valori 0 e 1. Questi valori vengono generati dalle espressioni GROUPING(ProductModelID) e GROUPING(p.Name).

X. Utilizzo dell'operatore ROLLUP

Nell'esempio seguente vengono riportati due esempi di codice. Nel primo vengono recuperati il nome del prodotto, il nome del cliente e la somma degli ordini eseguiti. Viene inoltre utilizzato l'operatore ROLLUP.

USE AdventureWorks ;
GO
SELECT ProductName, CustomerName, SUM(Orders) AS 'Sum orders'
FROM dbo.CubeExample
GROUP BY ProductName, CustomerName
WITH ROLLUP ;
GO

Set di risultati:

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)

Nel secondo esempio viene eseguita un'operazione ROLLUP nelle colonne delle società e dei reparti, viene quindi calcolato il numero totale di dipendenti.

L'operatore ROLLUP restituisce un riepilogo di funzioni di aggregazione. Ciò risulta utile quando sono necessarie informazioni di riepilogo ma un'operazione CUBE completa restituisce dati estranei, oppure quando sono presenti gruppi all'interno di altri gruppi, ad esempio i reparti di una società.

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

In questa query il nome della società, il reparto e la somma di tutti i dipendenti della società diventano parte del set di risultati, oltre ai calcoli dell'operatore ROLLUP.

USE AdventureWorks ;
GO
SELECT CompanyName, Department, SUM(NumEmployees)
FROM dbo.Personnel
GROUP BY CompanyName, Department WITH ROLLUP ;
GO

Set di risultati:

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. Utilizzo della funzione GROUPING

Nell'esempio seguente vengono aggiunte tre nuove righe alla tabella CubeExample. Ogni riga registra NULL in una o più colonne per illustrare che solo la funzione ROLLUP restituisce il valore 1 nella colonna di raggruppamento. Viene modificata, inoltre, l'istruzione SELECT utilizzata nell'esempio precedente.

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

È possibile utilizzare la funzione GROUPING solo con l'operatore CUBE o ROLLUP. La funzione GROUPING restituisce 1 quando un'espressione restituisce NULL, perché il valore di colonna è NULL e rappresenta il set di tutti i valori. La funzione GROUPING restituisce 0 quando la colona corrispondente, indipendentemente dal fatto che sia NULL o meno, non è un valore di sintassi ottenuto con l'opzione CUBE o ROLLUP. Il valore restituito è di tipo tinyint.

Set di risultati:

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. Utilizzo dell'istruzione SELECT con la clausola GROUP BY, una funzione di aggregazione e l'operatore ROLLUP

Nell'esempio seguente viene utilizzata una query SELECT contenente una funzione di aggregazione e una clausola 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

Nel set di risultati NULL tutti i valori di tale colonna.

Se l'istruzione SELECT viene utilizzata senza l'operatore ROLLUP, l'istruzione crea un singolo raggruppamento. Il valore restituito dalla query è una somma di ogni combinazione univoca delle colonne ProductModel, ProductModelID e ProductName.

ProductModel ProductModelID title SUM(qty)

È possibile utilizzare la funzione GROUPING con l'operatore ROLLUP o con l'operatore CUBE. È possibile applicare tale funzione a una delle colonne nell'elenco di selezione. La funzione restituisce 1 o 0 a seconda che la colonna venga o meno raggruppata dall'operatore ROLLUP.

a. Utilizzo dell'hint di ottimizzazione INDEX

Nell'esempio seguente vengono illustrati due diversi utilizzi dell'hint di ottimizzazione INDEX. Nel primo esempio viene illustrato come imporre l'utilizzo di un indice non cluster per il recupero di righe da una tabella, mentre nel secondo viene imposta una scansione di tabella in base all'indice 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. Utilizzo della clausola OPTION e degli hint GROUP

Nell'esempio seguente viene illustrato l'utilizzo della clausola OPTION (GROUP) con una clausola 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. Utilizzo dell'hint per la query UNION

Nell'esempio seguente viene utilizzato l'hint per la query MERGE UNION.

USE AdventureWorks ;
GO
SELECT *
FROM HumanResources.Employee e1
UNION
SELECT *
FROM HumanResources.Employee e2
OPTION (MERGE UNION) ;
GO

d. Utilizzo di un semplice operatore UNION

Nell'esempio seguente il set di risultati include il contenuto delle colonne ProductModelID e Name di entrambe le tabelle ProductModel e 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. Utilizzo di SELECT INTO con UNION

Nell'esempio seguente la clausola INTO nella seconda istruzione SELECT specifica che la tabella ProductResults contiene il set di risultati finale ottenuto con l'unione delle colonne designate delle tabelle ProductModel e Gloves. Si noti che la tabella Gloves viene creata nella prima istruzione 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. Utilizzo dell'operatore UNION in due istruzioni SELECT con la clausola ORDER BY

L'ordine di alcuni parametri utilizzati con la clausola UNION è importante. Nell'esempio seguente vengono illustrati l'utilizzo errato e quello corretto di UNION in due istruzioni SELECT in cui una colonna deve essere rinominata nell'output.

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. Utilizzo dell'operatore UNION in tre istruzioni SELECT per illustrare gli effetti dell'opzione ALL e delle parentesi

Negli esempi seguenti viene utilizzato l'operatore UNION per combinare i risultati di tre tabelle contenenti 5 righe di dati identiche. Nel primo esempio viene utilizzato UNION ALL per mostrare i record duplicati e vengono restituite tutte le 15 righe. Nel secondo esempio l'operatore UNION viene utilizzato senza l'opzione ALL per eliminare le righe duplicate dai risultati combinati delle tre istruzioni SELECT e vengono restituite 5 righe.

Nel terzo esempio viene utilizzata l'opzione ALL con il primo operatore UNION e il secondo operatore UNION, che non utilizza l'opzione ALL, viene racchiuso tra parentesi. Il secondo operatore UNION viene elaborato per primo in quanto è racchiuso tra parentesi e restituisce 5 righe in quanto l'opzione ALL è stata omessa e i duplicati vengono rimossi. Queste 5 righe vengono combinate con i risultati della prima istruzione SELECT mediante le parole chiave UNION ALL. I duplicati tra i due set di 5 righe non vengono rimossi. Il risultato finale include 10 righe.

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

Vedere anche

Riferimento

CREATE TRIGGER (Transact-SQL)
CREATE VIEW (Transact-SQL)
DELETE (Transact-SQL)
EXECUTE (Transact-SQL)
Espressioni (Transact-SQL)
INSERT (Transact-SQL)
LIKE (Transact-SQL)
UNION (Transact-SQL)
EXCEPT e INTERSECT (Transact-SQL)
UPDATE (Transact-SQL)
WHERE (Transact-SQL)

Altre risorse

Query distribuite
Nozioni fondamentali sulle subquery
Utilizzo di variabili e parametri (Motore di database)

Guida in linea e informazioni

Assistenza su SQL Server 2005

Cronologia modifiche

Versione Cronologia

14 aprile 2006

Nuovo contenuto:
  • Aggiunta di un esempio diverso per illustrare la modalità di utilizzo di LIKE nella clausola HAVING.