Partager via


Exemples SELECT (Transact-SQL)

Mis à jour : 14 avril 2006

Cette rubrique fournit des exemples illustrant l'utilisation de l'instruction SELECT.

A. Utilisation de SELECT pour extraire des lignes et des colonnes

L'exemple suivant présente trois exemples de code. Le premier exemple de code retourne toutes les lignes (aucune clause WHERE n'est définie) et toutes les colonnes (en utilisant *) de la table Product dans la base de données 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

Cet exemple retourne toutes les lignes (aucune clause WHERE n'est définie) et uniquement un sous-ensemble des colonnes (Name, ProductNumber, ListPrice) de la table Product dans la base de données AdventureWorks. En outre, un en-tête de colonne est ajouté.

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

Cet exemple retourne uniquement les lignes de Product dont la ligne de produits a pour valeur R et dont le nombre de jours nécessaires à la fabrication est inférieur à 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. Utilisation de SELECT avec des en-têtes de colonne et des calculs

Les exemples suivants retournent toutes les lignes de la table Product. Le premier exemple retourne le total des ventes et les remises pour chaque produit. Dans le second exemple, le gain est calculé pour chaque produit.

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

Voici la requête qui calcule le gain pour chaque produit dans chaque commande.

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. Utilisation de DISTINCT avec SELECT

L'exemple suivant utilise DISTINCT afin d'éviter l'extraction de titres en double.

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

D. Création de tables avec SELECT INTO

Le premier exemple suivant crée une table temporaire nommée #Bicycles dans tempdb. Pour utiliser cette table, faites-y toujours référence avec le nom exact indiqué. Celui-ci comprend le signe dièse (#).

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

Voici l'ensemble des résultats.

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

Ce second exemple crée la table 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

Voici l'ensemble des résultats.

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

E. Utilisation de sous-requêtes en corrélation

L'exemple suivant montre des requêtes sémantiquement équivalentes et illustre la différence entre l'utilisation du mot clé EXISTS et celle du mot clé IN. Les deux exemples illustrent une sous-requête valide qui extrait une instance de chaque nom de produit dont le modèle est un pull-over à manches longues (« long sleeve logo jersey »), et dont le numéro ProductModelID se trouve dans les tables Product et 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

L'exemple suivant utilise IN dans une sous-requête en corrélation ou répétitive. Il s'agit d'une requête dont les valeurs dépendent de la requête externe. La requête est exécutée de manière répétitive, une fois pour chaque ligne que la requête externe pourrait sélectionner. Cette requête extrait une instance du prénom et du nom de chaque employé dont la prime dans la table SalesPerson est égale à 5000.00 et dont le numéro d'identification d'employé se trouve dans les tables Employee et 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 sous-requête précédente de cette instruction ne peut pas être évaluée indépendamment de la requête externe. Elle requiert en effet une valeur pour Employee.EmployeeID, mais cette valeur change à mesure que le moteur de base de données SQL Server 2005 examine les différentes lignes de la table Employee.

Une sous-requête en corrélation peut également s'employer dans la clause HAVING d'une requête externe. Cet exemple recherche les modèles de produit dont le tarif maximum est supérieur au double du tarif moyen du modèle concerné.

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

Cet exemple utilise deux sous-requêtes en corrélation pour rechercher les noms des employés ayant vendu un produit spécifique.

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. Utilisation de GROUP BY

L'exemple suivant calcule le total de chaque commande dans la base de données.

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

Du fait de la clause GROUP BY, une seule ligne est retournée pour chaque commande et elle contient la somme de toutes les ventes de cette dernière.

G. Utilisation de GROUP BY avec plusieurs groupes

L'exemple suivant affiche le prix moyen et la somme des ventes annuelles cumulées, regroupés par ID de produit et ID d'offre spéciale :

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. Utilisation de GROUP BY et WHERE

L'exemple suivant présente les résultats en groupes après n'avoir extrait que les lignes avec des tarifs supérieurs à $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. Utilisation de GROUP BY avec une expression

L'exemple suivant effectue un regroupement en fonction d'une expression. Vous pouvez spécifier un regroupement en fonction d'une expression à condition qu'elle ne contienne pas de fonction d'agrégation.

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. Comparaison de GROUP BY et de GROUP BY ALL

Le premier exemple suivant génère des groupes uniquement pour les commandes dont les quantités sont > 10.

Le second exemple génère des groupes pour toutes les commandes.

La colonne qui contient la valeur d'agrégation (le prix moyen) présente une valeur NULL pour les groupes dont aucune ligne n'a été sélectionnée.

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. Utilisation de GROUP BY avec ORDER BY

L'exemple suivant calcule le prix moyen de chaque type de produit et trie les résultats en conséquence :

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. Utilisation de la clause HAVING

Le premier exemple suivant illustre une clause HAVING utilisée avec une fonction d'agrégation. Il regroupe les lignes de la table SalesOrderDetail par ID de produit et élimine ceux dont la quantité moyenne d'articles commandés est inférieure ou égale à cinq. Le second exemple illustre une clause HAVING sans fonction d'agrégation.

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

La requête suivante utilise la clause LIKE dans la clause HAVING :

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

M. Utilisation de HAVING et de GROUP BY

L'exemple suivant illustre l'utilisation des clauses GROUP BY, HAVING, WHERE et ORDER BY dans une instruction SELECT. Il génère des groupes et des valeurs résumées mais uniquement après avoir éliminé les produits dont le prix est supérieur à 25 $ et dont la quantité moyenne commandée est inférieure à 5. Il trie également les résultats par 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. Utilisation de HAVING avec SUM et AVG

L'exemple suivant regroupe la table SalesOrderDetail par ID de produit et ne comprend que les groupes de produits dont les commandes s'élèvent à plus de $1000000.00 et dont la quantité moyenne d'articles achetés est inférieure à 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

Pour visualiser les produits dont le total des ventes est supérieur à $2000000.00, utilisez la requête suivante :

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

Si vous souhaitez vous assurer qu'au moins mille cinq cents articles sont impliqués dans le calcul de chaque produit, utilisez l'instruction HAVING COUNT(*) > 1500 pour éliminer les produits dont le total renvoyé correspond à moins de 1500 articles vendus. La requête est la suivante :

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

O. Calcul de totaux de groupe avec COMPUTE BY

Les deux exemples de code suivants illustrent l'utilisation de COMPUTE BY. Le premier utilise COMPUTE BY avec une fonction d'agrégation et le second utilise COMPUTE BY avec deux fonctions d'agrégation.

Cette requête calcule la somme des commandes, pour les produits dont le prix est inférieur à $5.00, pour chaque type de produit.

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

Cette requête extrait le type de produit et le montant total commandé des produits dont le prix unitaire est inférieur à $5.00. La clause COMPUTE BY utilise deux fonctions d'agrégation différentes.

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. Calcul d'un total global à l'aide de COMPUTE sans BY

Le mot clé COMPUTE peut s'employer sans BY pour générer un total global, principal, etc...

L'exemple suivant calcule le total général des prix et des avances pour tous les types de produit dont le prix est inférieur à $2.00.

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

Vous pouvez utiliser COMPUTE BY et COMPUTE sans BY dans la même requête. La requête suivante calcule la somme des quantités commandées et des totaux par type de produit, puis le total général des quantités commandées et des totaux.

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. Calcul des sommes traitées sur toutes les lignes

L'exemple suivant utilise seulement trois colonnes dans la liste de sélection et donne des totaux basés sur toutes les quantités commandées et sur tous les totaux à la suite des résultats.

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

R. Utilisation de plusieurs clauses COMPUTE

L'exemple suivant calcule la somme des montants de toutes les commandes dont le prix unitaire est inférieur à 5 $, par ID de produit et quantité commandée, ainsi que la somme des montants de toutes les commandes inférieures à 5 $, uniquement par ID de produit. Vous pouvez utiliser plusieurs fonctions d'agrégation dans la même instruction en employant plusieurs clauses 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. Comparaison de GROUP BY et de COMPUTE

Le premier exemple suivant utilise la clause COMPUTE pour calculer la somme de toutes les commandes dont le prix unitaire du produit est inférieur à $5.00, par type de produit. Le second exemple fournit la même information en n'utilisant que GROUP BY.

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

Voici la seconde requête qui utilise 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. Utilisation de SELECT avec les clauses GROUP BY, COMPUTE et ORDER BY

L'exemple suivant retourne uniquement les commandes dont le prix unitaire est inférieur à 5 $, puis calcule la somme totale par produit et le total général. Toutes les colonnes traitées s'affichent dans la liste de sélection.

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. Utilisation de l'instruction SELECT avec CUBE

L'exemple suivant présente deux exemples de code. Le premier exemple retourne un ensemble de résultats d'une instruction SELECT à l'aide de l'opérateur CUBE. Grâce à l'opérateur CUBE, l'instruction retourne une ligne supplémentaire.

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

La valeur NULL représente toutes les valeurs de la colonne ProductID. Le jeu de résultats retourne des valeurs pour la quantité vendue de chaque produit et pour la quantité totale vendue de l'ensemble des produits. Le même résultat est retourné que vous utilisiez l'opérateur CUBE ou l'opérateur ROLLUP.

L'exemple suivant utilise la table CubeExample pour démontrer l'effet de l'opérateur CUBE sur l'ensemble de résultats et utilise une fonction d'agrégation (SUM). La table CubeExample contient un nom de produit, un nom de client et le nombre de commandes passées par chaque client pour un même produit.

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

Exécutez d'abord une requête classique avec une clause GROUP BY et l'ensemble de résultats.

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

La clause GROUP BY amène l'ensemble de résultats à former des groupes au sein des groupes.

Voici l'ensemble des résultats.

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)

Exécutez ensuite une requête avec une clause GROUP BY, en utilisant l'opérateur CUBE. L'ensemble de résultats doit inclure les mêmes informations ainsi que des informations de super-agrégation pour chacune des colonnes GROUP BY.

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

L'ensemble de résultats de l'opérateur CUBE contient les valeurs provenant de l'ensemble de résultats GROUP BY simple précédent et ajoute les super-agrégations à chaque colonne de la clause GROUP BY. NULL représente toutes les valeurs de l'ensemble servant à calculer l'agrégation.

Voici l'ensemble des résultats.

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 ligne 4 du jeu de résultats indique que l'ensemble des clients a passé un total de 150 commandes pour le produit Filo Mix.

La ligne 11 du jeu de résultats indique que l'ensemble des clients a passé un total de 260 commandes pour tous les produits.

Les lignes 12 à 14 de l'ensemble de résultats indiquent que chaque client a respectivement passé 100, 110 et 50 commandes pour tous les produits.

V. Utilisation de l'opérateur CUBE sur un jeu de résultats à trois colonnes

Dans l'exemple suivant, l'instruction SELECT retourne l'ID du modèle de produit, le nom du produit et la quantité de commandes. La clause GROUP BY de cet exemple comprend les colonnes ProductModelID et Name .

Grâce à l'opérateur CUBE, le jeu de résultats contient davantage d'informations détaillées sur les quantités de commandes de produits et de modèles de produit. NULL représente toutes les valeurs de la colonne title.

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

L'augmentation du nombre de colonnes dans la clause GROUP BY indique pourquoi l'opérateur CUBE est un opérateur à n dimensions. Une clause GROUP BY portant sur deux colonnes retourne trois groupes en plus lorsque l'opérateur CUBE est utilisé. Le nombre de groupes peut être supérieur à trois, suivant les différentes valeurs figurant dans les colonnes.

Le jeu de résultats est regroupé par ID du modèle de produit puis par nom du produit.

La valeur NULL dans la colonne ProductModelID représente tous les ProductModels. La valeur NULL dans la colonne Name représente tous les Products. L'opérateur CUBE retourne les groupes suivants d'informations à partir d'une instruction SELECT :

  • Quantité de commandes pour chaque modèle de produit
  • Quantité de commandes pour chaque produit
  • Nombre total de commandes

Chaque colonne référencée dans la clause GROUP BY a fait l'objet d'une référence croisée avec toutes les autres colonnes de la clause GROUP BY et la fonction d'agrégation SUM a été réappliquée. Cela augmente le nombre de lignes dans le jeu de résultats. Les informations retournées dans le jeu de résultats augmentent de n-dimensions parallèlement au nombre de colonnes de la clause GROUP BY.

ms187731.note(fr-fr,SQL.90).gifRemarque :
Vérifiez que les colonnes qui suivent la clause GROUP BY entretiennent des relations significatives et réelles. Par exemple, si vous utilisez Name et ProductID, l'opérateur CUBE retourne des informations sans intérêt. L'utilisation de l'opérateur CUBE sur une structure existante, telle que des ventes annuelles ou trimestrielles, entraîne la création de lignes dénuées de sens dans le jeu de résultats. Il est plus efficace d'utiliser l'opérateur ROLLUP.

W. Utilisation de la fonction GROUPING avec l'opérateur CUBE

L'exemple suivant montre comment l'instruction SELECT utilise la fonction d'agrégation SUM, la clause GROUP BY et l'opérateur CUBE. Elle utilise également la fonction GROUPING sur les deux colonnes mentionnées après la clause 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

Le jeu de résultats possède deux colonnes qui contiennent des valeurs 0 et 1. Celles-ci sont produites par les expressions GROUPING(ProductModelID) et GROUPING(p.Name).

X. Utilisation de l'opérateur ROLLUP

L'exemple suivant présente deux exemples de code. Le premier exemple extrait le nom du produit, le nom du client et la somme des commandes passées ; il utilise l'opérateur ROLLUP.

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

Voici l'ensemble des résultats.

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)

Le second exemple suivant exécute une opération ROLLUP sur les colonnes company et department, et calcule le nombre total d'employés.

L'opérateur ROLLUP produit un résumé des agrégats. Ceci est utile lorsque vous avez besoin d'informations de résumé et qu'un opérateur CUBE fournit des données superflues, ou lorsque vous avez des ensembles à l'intérieur d'ensembles. À titre d'exemple, les services d'une société constituent un ensemble dans un ensemble.

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

Dans la requête suivante, le nom de la société, le service et le nombre d'employés de la société deviennent une partie du jeu de résultats, en plus des calculs de l'opérateur ROLLUP.

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

Voici l'ensemble des résultats.

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. Utilisation de la fonction GROUPING

L'exemple suivant ajoute trois nouvelles lignes à la table CubeExample. Chacun des trois enregistrements NULL, situés dans une ou plusieurs colonnes, n'affiche que la fonction ROLLUP qui produit une valeur de 1 dans la colonne de regroupement. En outre, cet exemple modifie l'instruction SELECT qui était utilisée dans l'exemple précédent.

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 fonction GROUPING ne peut être utilisée qu'avec CUBE ou ROLLUP. Elle retourne 1 quand le résultat d'une expression est NULL parce que la valeur de la colonne est NULL et représente l'ensemble de toutes les valeurs. Elle retourne 0 lorsque la colonne correspondante, qu'il s'agisse de NULL ou non, ne provient pas de CUBE ou de ROLLUP. Le type de données de la valeur retournée est tinyint.

Voici l'ensemble des résultats.

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. Utilisation de SELECT avec GROUP BY, une fonction d'agrégation et ROLLUP

L'exemple suivant utilise une requête SELECT qui contient une fonction d'agrégation et une clause 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

Dans le jeu de résultats, la valeur NULL représente toutes les valeurs de cette colonne.

Si vous utilisez l'instruction SELECT sans l'opérateur ROLLUP, l'instruction crée un seul regroupement. La requête retourne une valeur totale pour chaque combinaison unique de ProductModel, ProductModelID et ProductName :

ProductModel ProductModelID title SUM(qty)

La fonction GROUPING peut s'utiliser avec l'opérateur ROLLUP ou avec l'opérateur CUBE. Vous pouvez appliquer cette fonction à l'une des colonnes de la liste de sélection. La fonction retourne 1 ou 0 en fonction du fait que la colonne soit groupée ou non par l'opérateur ROLLUP.

a. Utilisation de l'indicateur d'optimiseur INDEX

L'exemple suivant illustre deux manières d'utiliser l'indicateur d'optimiseur INDEX. Le premier exemple illustre la façon d'obliger l'optimiseur à utiliser un index non-cluster pour extraire des lignes d'une table, tandis que le second exemple génère l'analyse d'une table à l'aide d'un index 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. Utilisation de la clause OPTION avec les indicateurs GROUP

L'exemple suivant montre comment la clause OPTION (GROUP) est utilisée avec une clause 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. Utilisation de l'indicateur de requête UNION

L'exemple suivant utilise l'indicateur de requête MERGE UNION.

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

d. Utilisation de l'opérateur UNION simple

Dans l'exemple suivant, le jeu de résultats comprend le contenu des colonnes ProductModelID et Name des deux tables ProductModel et 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. Utilisation de SELECT INTO avec UNION

Dans l'exemple suivant, la clause INTO de la seconde instruction SELECT indique que la table nommée ProductResults contient le jeu de résultats final de l'union des colonnes désignées des tables ProductModel et Gloves. La table Gloves est créée dans la première instruction 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. Utilisation de l'opérateur UNION dans deux instructions SELECT avec ORDER BY

L'ordre de certains paramètres utilisés avec la clause UNION est important. L'exemple suivant illustre l'utilisation incorrecte et correcte de UNION dans deux instructions SELECT où une colonne doit être renommée dans le résultat.

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. Utilisation de l'opérateur UNION dans trois instructions SELECT pour illustrer les effets de ALL et des parenthèses

Les exemples suivants utilisent UNION pour combiner les résultats de trois tables, ayant chacune les 5 lignes de données identiques. Le premier exemple utilise UNION ALL pour montrer les doublons d'enregistrement et retourne l'ensemble des 15 lignes. Le deuxième exemple utilise UNION sans ALL pour éliminer les doublons de ligne des résultats combinés des trois instructions SELECT et retourne 5 lignes.

Le troisième exemple utilise ALL avec la première clause UNION et met entre parenthèses la seconde clause UNION qui n'utilise pas ALL. La seconde clause UNION est traitée en premier, car elle est entre parenthèses. Elle retourne 5 lignes car l'option ALL n'est pas utilisée et les doublons sont supprimés. Ces 5 lignes sont combinées avec les résultats de la première instruction SELECT à l'aide des mots clés UNION ALL. Cela ne supprime pas les doublons entre les deux ensembles de 5 lignes. Le résultat final contient 10 lignes.

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

Voir aussi

Référence

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

Autres ressources

Requêtes distribuées
Principes de base des sous-requêtes
Utilisation de variables et de paramètres (moteur de base de données)

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications

Version Historique

14 avril 2006

Nouveau contenu :
  • Insertion d'un exemple différent pour illustrer l'utilisation de LIKE dans la clause HAVING.