esempi edizione Standard LECT (Transact-SQL)

  • Articolo

Si applica a:SQL Server database SQL di Azure Istanza gestita di SQL di Azure

Questo articolo fornisce esempi di utilizzo dell'istruzione edizione Standard LECT.

Questo articolo richiede il AdventureWorks2022 database di esempio, che è possibile scaricare dalla home page degli esempi di Microsoft SQL Server e dei progetti della community.

R. Usare edizione Standard LECT per recuperare 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 AdventureWorks2022.

USE AdventureWorks2022;
GO

SELECT *
FROM Production.Product
ORDER BY Name ASC;

-- Alternate way.
USE AdventureWorks2022;
GO

SELECT p.*
FROM Production.Product AS 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 AdventureWorks2022. Viene aggiunta, inoltre, un'intestazione di colonna.

USE AdventureWorks2022;
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 AdventureWorks2022;
GO

SELECT Name,
    ProductNumber,
    ListPrice AS Price
FROM Production.Product
WHERE ProductLine = 'R'
    AND DaysToManufacture < 4
ORDER BY Name ASC;
GO

B. Usare edizione Standard LECT con intestazioni di colonna e calcoli

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 AdventureWorks2022;
GO

SELECT p.Name AS ProductName,
    NonDiscountSales = (OrderQty * UnitPrice),
    Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS 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 AdventureWorks2022;
GO

SELECT 'Total income is',
    ((OrderQty * UnitPrice) * (1.0 - UnitPriceDiscount)),
    ' for ',
    p.Name AS ProductName
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
    ON p.ProductID = sod.ProductID
ORDER BY ProductName ASC;
GO

C. Usare DISTINCT con edizione Standard LECT

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

USE AdventureWorks2022;
GO

SELECT DISTINCT JobTitle
FROM HumanResources.Employee
ORDER BY JobTitle;
GO

D. Creare tabelle con edizione Standard LECT INTO

Nel primo esempio seguente viene creata una tabella temporanea denominata #Bicycles in tempdb.

USE tempdb;
GO

IF OBJECT_ID(N'#Bicycles', N'U') IS NOT NULL
DROP TABLE #Bicycles;
GO

SELECT *
INTO #Bicycles
FROM AdventureWorks2022.Production.Product
WHERE ProductNumber LIKE 'BK%';
GO

Nel secondo esempio viene creata la tabella permanente NewProducts.

USE AdventureWorks2022;
GO

IF OBJECT_ID('dbo.NewProducts', 'U') IS NOT NULL
DROP TABLE dbo.NewProducts;
GO

ALTER DATABASE AdventureWorks2022 SET RECOVERY BULK_LOGGED;
GO

SELECT *
INTO dbo.NewProducts
FROM Production.Product
WHERE ListPrice > $25
    AND ListPrice < $100;
GO

ALTER DATABASE AdventureWorks2022 SET RECOVERY FULL;
GO

E. Usare sottoquery correlate

Una query correlata è una query i cui valori dipendono dalla query esterna. Questa query può essere eseguita ripetutamente, una volta per ogni riga che può essere selezionata dalla query esterna.

Nel primo esempio vengono illustrate query semanticamente equivalenti per evidenziare la differenza tra l'uso della parola chiave EXISTS e della parola chiave IN. Entrambi sono esempi di sottoquery 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 AdventureWorks2022;
GO

SELECT DISTINCT Name
FROM Production.Product AS p
WHERE EXISTS (
    SELECT *
    FROM Production.ProductModel AS pm
    WHERE p.ProductModelID = pm.ProductModelID
        AND pm.Name LIKE 'Long-Sleeve Logo Jersey%'
);
GO

-- OR
USE AdventureWorks2022;
GO

SELECT DISTINCT Name
FROM Production.Product
WHERE ProductModelID IN (
    SELECT ProductModelID
    FROM Production.ProductModel AS pm
    WHERE p.ProductModelID = pm.ProductModelID
        AND Name LIKE 'Long-Sleeve Logo Jersey%'
);
GO

Nell'esempio seguente viene IN usata e recuperata un'istanza del nome e del nome della famiglia di ogni dipendente per cui il bonus nella SalesPerson tabella è 5000.00e per cui i numeri di identificazione dei dipendenti corrispondono nelle Employee tabelle e SalesPerson .

USE AdventureWorks2022;
GO

SELECT DISTINCT p.LastName,
    p.FirstName
FROM Person.Person AS p
INNER JOIN HumanResources.Employee AS e
    ON e.BusinessEntityID = p.BusinessEntityID
WHERE 5000.00 IN (
    SELECT Bonus
    FROM Sales.SalesPerson AS sp
    WHERE e.BusinessEntityID = sp.BusinessEntityID
);
GO

La sottoquery precedente in questa istruzione non può essere valutata indipendentemente dalla query esterna. Richiede un valore per Employee.EmployeeID, ma questo valore cambia quando il motore di database di SQL Server esamina righe diverse della tabella Employee.

È inoltre possibile inserire una sottoquery 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 AdventureWorks2022;
GO

SELECT p1.ProductModelID
FROM Production.Product AS p1
GROUP BY p1.ProductModelID
HAVING MAX(p1.ListPrice) >= (
    SELECT AVG(p2.ListPrice) * 2
    FROM Production.Product AS p2
    WHERE p1.ProductModelID = p2.ProductModelID
);
GO

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

USE AdventureWorks2022;
GO

SELECT DISTINCT pp.LastName,
    pp.FirstName
FROM Person.Person pp
INNER JOIN HumanResources.Employee e
    ON e.BusinessEntityID = pp.BusinessEntityID
WHERE pp.BusinessEntityID 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. Usare GROUP BY

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

USE AdventureWorks2022;
GO

SELECT SalesOrderID,
    SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
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. Usare 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 AdventureWorks2022;
GO

SELECT ProductID,
    SpecialOfferID,
    AVG(UnitPrice) AS [Average Price],
    SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY ProductID,
    SpecialOfferID
ORDER BY ProductID;
GO

H. Usare 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 AdventureWorks2022;
GO

SELECT ProductModelID,
    AVG(ListPrice) AS [Average List Price]
FROM Production.Product
WHERE ListPrice > $1000
GROUP BY ProductModelID
ORDER BY ProductModelID;
GO

I. Usare GROUP BY con un'espressione

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

USE AdventureWorks2022;
GO

SELECT AVG(OrderQty) AS [Average Quantity],
    NonDiscountSales = (OrderQty * UnitPrice)
FROM Sales.SalesOrderDetail
GROUP BY (OrderQty * UnitPrice)
ORDER BY (OrderQty * UnitPrice) DESC;
GO

J. Usare GROUP BY con 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 AdventureWorks2022;
GO

SELECT ProductID,
    AVG(UnitPrice) AS [Average Price]
FROM Sales.SalesOrderDetail
WHERE OrderQty > 10
GROUP BY ProductID
ORDER BY AVG(UnitPrice);
GO

K. Usare la 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 AdventureWorks2022;
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 AdventureWorks2022;
GO
SELECT SalesOrderID, CarrierTrackingNumber
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID, CarrierTrackingNumber
HAVING CarrierTrackingNumber LIKE '4BD%'
ORDER BY SalesOrderID ;
GO

.L Usare 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 AdventureWorks2022;
GO

SELECT ProductID
FROM Sales.SalesOrderDetail
WHERE UnitPrice < 25.00
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID;
GO

M. Usare HAVING con 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 AdventureWorks2022;
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, usare questa query:

USE AdventureWorks2022;
GO

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

Se si desidera assicurarsi che ci siano almeno 1.500 articoli coinvolti nei calcoli per ogni prodotto, utilizzare HAVING COUNT(*) > 1500 per eliminare i prodotti che restituiscono totali per meno di 1500 articoli venduti. La query appare come la seguente:

USE AdventureWorks2022;
GO

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

N. Usare l'hint index optimizer

Nell'esempio seguente vengono illustrati due diversi utilizzi dell'hint di ottimizzazione INDEX. Il primo esempio mostra come forzare l'ottimizzazione a usare un indice non cluster per recuperare righe da una tabella. Il secondo esempio forza l'analisi di una tabella usando un indice pari a 0.

USE AdventureWorks2022;
GO

SELECT pp.FirstName,
    pp.LastName,
    e.NationalIDNumber
FROM HumanResources.Employee AS e WITH (INDEX (AK_Employee_NationalIDNumber))
INNER JOIN Person.Person AS pp
    ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO

-- Force a table scan by using INDEX = 0.
USE AdventureWorks2022;
GO

SELECT pp.LastName,
    pp.FirstName,
    e.JobTitle
FROM HumanResources.Employee AS e WITH (INDEX = 0)
INNER JOIN Person.Person AS pp
    ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO

M. Usare OPTION e gli hint GROUP

Nell'esempio seguente viene illustrato l'utilizzo della clausola OPTION (GROUP) con una clausola GROUP BY.

USE AdventureWorks2022;
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

.O Usare l'hint per la query UNION

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

USE AdventureWorks2022;
GO

SELECT BusinessEntityID,
    JobTitle,
    HireDate,
    VacationHours,
    SickLeaveHours
FROM HumanResources.Employee AS e1

UNION

SELECT BusinessEntityID,
    JobTitle,
    HireDate,
    VacationHours,
    SickLeaveHours
FROM HumanResources.Employee AS e2
OPTION (MERGE UNION);
GO

P. Usare un'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 AdventureWorks2022;
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 AdventureWorks2022;
GO

SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)

UNION

SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name;
GO

D. Usare edizione Standard LECT 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. La tabella Gloves viene creata nella prima istruzione SELECT.

USE AdventureWorks2022;
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 AdventureWorks2022;
GO

SELECT ProductModelID, Name
INTO dbo.ProductResults
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)

UNION

SELECT ProductModelID, Name
FROM dbo.Gloves;
GO

SELECT ProductModelID, Name
FROM dbo.ProductResults;

.R Usare UNION di due istruzioni edizione Standard LECT con 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 AdventureWorks2022;
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 AdventureWorks2022;
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 AdventureWorks2022;
GO

SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)

UNION

SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name;
GO

S. Usare UNION di tre istruzioni edizione Standard LECT per visualizzare gli effetti di ALL e parentesi

Negli esempi seguenti viene usato UNION per combinare i risultati di tre tabelle con le stesse cinque righe di dati. Nel primo esempio viene utilizzato UNION ALL per mostrare i record duplicati e vengono restituite tutte le 15 righe. Il secondo esempio usa UNION senza ALL per eliminare le righe duplicate dai risultati combinati delle tre SELECT istruzioni e restituisce cinque righe.

Il terzo esempio usa ALL con il primo operatore UNION e il secondo operatore UNION, che non usa ALL, viene racchiuso tra parentesi. Il secondo UNION viene elaborato per primo perché è racchiuso tra parentesi e restituisce cinque righe perché l'opzione ALL non viene usata e i duplicati vengono rimossi. Queste cinque righe vengono combinate con i risultati del primo SELECT usando le UNION ALL parole chiave . Questo esempio non rimuove i duplicati tra i due set di cinque righe. Il risultato finale include 10 righe.

USE AdventureWorks2022;
GO

IF OBJECT_ID('dbo.EmployeeOne', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeOne;
GO

IF OBJECT_ID('dbo.EmployeeTwo', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeTwo;
GO

IF OBJECT_ID('dbo.EmployeeThree', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeThree;
GO

SELECT pp.LastName, pp.FirstName, e.JobTitle
INTO dbo.EmployeeOne
FROM Person.Person AS pp
INNER JOIN HumanResources.Employee AS e
    ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO

SELECT pp.LastName, pp.FirstName, e.JobTitle
INTO dbo.EmployeeTwo
FROM Person.Person AS pp
INNER JOIN HumanResources.Employee AS e
    ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO

SELECT pp.LastName, pp.FirstName, e.JobTitle
INTO dbo.EmployeeThree
FROM Person.Person AS pp
INNER JOIN HumanResources.Employee AS e
    ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO

-- Union ALL
SELECT LastName, FirstName, JobTitle
FROM dbo.EmployeeOne

UNION ALL

SELECT LastName, FirstName, JobTitle
FROM dbo.EmployeeTwo

UNION ALL

SELECT LastName, FirstName, JobTitle
FROM dbo.EmployeeThree;
GO

SELECT LastName, FirstName, JobTitle
FROM dbo.EmployeeOne

UNION

SELECT LastName, FirstName, JobTitle
FROM dbo.EmployeeTwo

UNION

SELECT LastName, FirstName, JobTitle
FROM dbo.EmployeeThree;
GO

SELECT LastName, FirstName, JobTitle
FROM dbo.EmployeeOne

UNION ALL

(
    SELECT LastName, FirstName, JobTitle
    FROM dbo.EmployeeTwo

    UNION

    SELECT LastName, FirstName, JobTitle
    FROM dbo.EmployeeThree
);
GO

Contenuto correlato