Freigeben über


SELECT-Beispiele (Transact-SQL)

Aktualisiert: 14. April 2006

In diesem Thema werden Beispiele für die Verwendung der SELECT-Anweisung bereitgestellt.

A. Verwenden von SELECT zum Abrufen von Zeilen und Spalten

Im folgenden Beispiel werden drei Codebeispiele aufgeführt. Im ersten Codebeispiel werden alle Zeilen (es ist keine WHERE-Klausel angegeben) und alle Spalten (mit *) aus der Product-Tabelle in der AdventureWorks-Datenbank zurückgegeben.

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

In diesem Beispiel werden alle Zeilen (keine WHERE-Klausel angegeben) und nur eine Teilmenge der Spalten (Name, ProductNumber, ListPrice) aus der Product-Tabelle in der AdventureWorks-Datenbank zurückgegeben. Außerdem werden Spaltenüberschriften hinzugefügt.

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

In diesem Beispiel werden nur die Zeilen für Product zurückgegeben, die über die Produktgruppe R verfügen und für die die Anzahl von Fertigungstagen weniger als 4 beträgt.

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

B. Verwenden von SELECT mit Spaltenüberschriften und Berechnungen

In diesen Beispielen werden alle Zeilen aus der Product-Tabelle zurückgegeben. Im ersten Beispiel werden die Gesamtumsatzzahlen und die Rabatte für jedes Produkt zurückgegeben. Im zweiten Beispiel werden die gesamten Einnahmen für jedes Produkt berechnet.

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

Diese Abfrage berechnet die Einnahmen für jedes Produkt in dem jeweiligen Kaufauftrag.

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. Verwenden von DISTINCT mit SELECT

Im folgenden Beispiel wird DISTINCT verwendet, um das Abrufen von doppelten Titeln zu verhindern.

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

D. Erstellen von Tabellen mit SELECT INTO

Im ersten Beispiel wird eine temporäre Tabelle namens #Bicycles in tempdb erstellt. Wenn Sie diese Tabelle verwenden möchten, müssen Sie immer den exakten Namen angeben, der gezeigt wird. Dazu zählt auch das Nummernzeichen (#).

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

Dies ist das Resultset.

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

In diesem zweiten Beispiel wird eine dauerhafte Tabelle namens NewProducts erstellt.

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

Dies ist das Resultset.

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

E. Verwenden von abhängigen Unterabfragen

Im folgenden Beispiel werden Abfragen, die semantisch ähnlich sind, gezeigt und der Unterschied zwischen dem Verwenden des EXISTS-Schlüsselwortes und des IN-Schlüsselwortes veranschaulicht. In beiden Fällen handelt es sich um eine gültige Unterabfrage, die eine Instanz von jedem Produktnamen abruft, für den als Produktmodell ein langärmeliges Logo-T-Shirt angegeben ist und für den die ProductModelID-Nummern in den Product- und ProductModel-Tabellen übereinstimmen.

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

Im folgenden Beispiel wird IN in einer abhängigen oder sich wiederholenden Unterabfrage verwendet. Die Werte dieser Abfrage sind von der äußeren Abfrage abhängig. Die Abfrage wird wiederholt ausgeführt, und zwar einmal für jede Zeile, die von der äußeren Abfrage ausgewählt wird. Diese Abfrage ruft eine Instanz des Vor- und Nachnamens der einzelnen Mitarbeiter ab, für die der Bonus in der SalesPerson-Tabelle 5000.00 beträgt und für die die Mitarbeiter-IDs in der Employee- und SalesPerson-Tabelle übereinstimmen.

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

Die vorherige Unterabfrage in dieser Anweisung kann nicht unabhängig von der äußeren Abfrage ausgewertet werden. Sie benötigt einen Wert für Employee.EmployeeID, wobei sich dieser Wert jedoch ändert, während SQL Server 2005-Datenbankmodul unterschiedliche Zeilen in Employee untersucht.

Eine abhängige Unterabfrage kann auch in der HAVING-Klausel einer äußeren Abfrage verwendet werden. Mit diesem Beispiel können Sie die Produktmodelle ermitteln, bei denen der maximale Listenpreis mehr als doppelt so hoch ist wie der Durchschnittpreis für das Modell.

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 diesem Beispiel werden zwei abhängige Unterabfragen verwendet, um die Namen von Mitarbeitern zu finden, die ein bestimmtes Produkt verkauft haben.

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. Verwenden von GROUP BY

Im folgenden Beispiel wird die jeweilige Summe der einzelnen Kaufaufträge in der Datenbank ermittelt.

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

Wegen der GROUP BY-Klausel wird für jede Bestellung nur eine Zeile zurückgegeben, die die Summe aller Kaufaufträge enthält.

G. Verwenden von GROUP BY mit mehreren Gruppen

Dieses Beispiel ermittelt den Durchschnittspreis und die Summe der Jahr bis Datum-Verkäufe, die nach Produkt-ID und Sonderangebots-ID gruppiert sind:

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. Verwenden von GROUP BY und WHERE

In diesem Beispiel werden die Ergebnisse in Gruppen zusammengefasst, nachdem nur die Zeilen mit Listenpreisen von mehr als $1000 abgerufen wurden.

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. Verwenden von GROUP BY mit einem Ausdruck

Im folgenden Beispiel wird nach einem Ausdruck gruppiert. Sie können nach einem Ausdruck gruppieren, wenn dieser keine Aggregatfunktionen enthält.

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. Vergleichen von GROUP BY und GROUP BY ALL

Im ersten Beispiel werden nur Gruppen für Bestellungen erzeugt, die eine Bestellmenge von > 10 aufweisen.

Im zweiten Beispiel werden Gruppen für alle Bestellungen erzeugt.

Die Spalte, die den Aggregatwert (den Durchschnittspreis) aufnimmt, enthält NULL für Gruppen, zu denen keine gekennzeichneten Zeilen vorhanden sind.

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. Verwenden von GROUP BY mit ORDER BY

Im folgenden Beispiel wird zu jedem Produkttyp der Durchschnittspreis ermittelt, nach dem anschließend die Ergebnisse geordnet werden:

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. Verwenden der HAVING-Klausel

Im ersten Beispiel wird eine HAVING-Klausel mit einer Aggregatfunktion gezeigt. Sie gruppiert die Zeilen in der SalesOrderDetail-Tabelle nach Produkt-ID und entfernt Produkte, deren durchschnittliche Bestellmenge mit 5 oder weniger angegeben ist. Im zweiten Beispiel wird eine HAVING-Klausel ohne Aggregatfunktionen gezeigt.

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

Diese Abfrage verwendet die LIKE-Klausel in der HAVING-Klausel.

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

M. Verwenden von HAVING und GROUP BY

Im folgenden Beispiel wird die Verwendung der Klauseln GROUP BY, HAVING, WHERE und ORDER BY in einer SELECT-Anweisung gezeigt. Sie erzeugt Gruppen und Summenwerte, jedoch erst, nachdem alle Produkte entfernt wurden, deren Preise über 25 $ liegen und für die Bestellmengen unter 5 angegeben sind. Außerdem werden die Ergebnisse nach der ProductID-Spalte sortiert:

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

N. Verwenden von HAVING mit SUM und AVG

Im folgenden Beispiel wird die SalesOrderDetail-Tabelle nach Produkt-ID gruppiert und nur die Produktgruppen eingeschlossen, für die Bestellungen mit einem Gesamtwert von mehr als $1000000.00 vorliegen und deren durchschnittliche Bestellmenge mit weniger als 3 angegeben ist.

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

Mit der folgenden Abfrage können Sie alle Produkte abfragen, für die Verkäufe im Gesamtwert von über $2000000.00 verzeichnet wurden:

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

Wenn Sie sicherstellen möchten, dass in die Berechnungen zu jedem Produkt mindestens 1.500 Artikel einfließen, entfernen Sie mit HAVING COUNT(*) > 1500 die Produkte, die Summen mit weniger als 1500 verkauften Artikeln zurückgeben. Die Abfrage lautet folgendermaßen:

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

O. Berechnen von Gesamtwerten für Gruppen mithilfe von COMPUTE BY

Im folgenden Beispiel wird mithilfe von zwei Codebeispielen die Verwendung von COMPUTE BY veranschaulicht. Im ersten Beispiel wird eine COMPUTE BY-Anweisung mit einer Aggregatfunktion verwendet, während im zweiten Beispiel ein COMPUTE BY-Element und zwei Aggregatfunktionen verwendet werden.

Diese Abfrage berechnet für jeden Produkttyp die Bestellsumme für Produkte, die weniger als $5.00 kosten.

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

Diese Abfrage ruft den Produkttyp und die Gesamtsumme der Bestellungen für Produkte mit einem Stückpreis unter $5.00 ab. Die COMPUTE BY-Klausel verwendet zwei verschiedene Aggregatfunktionen.

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. Berechnen von Gesamtwerten mithilfe von COMPUTE ohne BY

Das COMPUTE-Schlüsselwort kann ohne BY verwendet werden, um Gesamtsummen, Gesamtanzahlen usw. zu generieren.

Im folgenden Beispiel wird für alle Produkttypen, die weniger als $2.00 kosten, die Gesamtsumme der Preise und der Vorschüsse ermittelt:

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

Sie können COMPUTE BY und COMPUTE ohne BY auch in derselben Abfrage verwenden. Die folgende Abfrage ermittelt die Summe der Bestellmengen und Zeilensummen nach Produkttyp und berechnet dann die Gesamtsumme der Bestellmengen und Zeilensummen.

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. Berechnen von Summen in allen Zeilen

Im folgenden Beispiel werden nur drei Spalten in der Auswahlliste angegeben und am Ende der Ergebnisse Gesamtsummen für alle Bestellmengen und Zeilensummen angezeigt.

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

R. Verwenden von mehreren COMPUTE-Klauseln

Im folgenden Beispiel wird (nach Produkt-ID und Bestellmenge sortiert) der Gesamtpreis aller Bestellungen mit einem Stückpreis von weniger als 5 $ und (nur nach Produkt-ID sortiert) der Gesamtpreis aller Bestellungen unter 5 $ ermittelt. Sie können in einer Anweisung verschiedene Aggregatfunktionen verwenden, indem Sie mehrere COMPUTE BY-Klauseln einbeziehen.

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. Vergleichen von GROUP BY mit COMPUTE

Im ersten Beispiel wird die COMPUTE-Klausel verwendet, um (nach Produkttyp sortiert) die Summe aller Bestellungen zu berechnen, bei denen der Stückpreis des jeweiligen Produkts weniger als $5.00 beträgt. Im zweiten Beispiel werden die gleichen Summendaten nur mithilfe von GROUP BY erzeugt.

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

Dies ist die zweite Abfrage, in der GROUP BY verwendet wird.

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

T. Verwenden von SELECT mit GROUP BY-, COMPUTE- und ORDER BY-Klauseln

Im folgenden Beispiel werden nur die Bestellungen zurückgegeben, bei denen der Stückpreis unter 5 $ liegt. Anschließend berechnet das Beispiel die Zeilensumme nach Produkt und die Gesamtsumme. Alle berechneten Spalten werden in der Auswahlliste angezeigt.

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. Verwenden der SELECT-Anweisung mit CUBE

Im folgenden Beispiel werden zwei Codebeispiele aufgeführt. Im ersten Beispiel wird ein Resultset aus einer SELECT-Anweisung mithilfe des CUBE-Operators zurückgegeben. Mithilfe des CUBE-Operators gibt die Anweisung eine zusätzliche Zeile zurück.

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 steht für alle Werte in der ProductID-Spalte. Das Resultset gibt die Verkaufszahlen für jedes einzelne Produkt sowie die Summe aller verkauften Produkte zurück. Der ROLLUP-Operator würde in diesem Beispiel dasselbe Ergebnis wie der CUBE-Operator zurückgeben.

Im folgenden Beispiel werden die CubeExample-Tabelle verwendet, um zu zeigen, wie sich der CUBE-Operator auf das Resultset auswirkt und eine Aggregatfunktion (SUM) verwendet. Die CubeExample-Tabelle enthält einen Produktnamen, einen Kundennamen und die Anzahl von Bestellungen, die jeder Kunde für ein bestimmtes Produkt aufgegeben hat.

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

Zuerst wird eine standardmäßige Abfrage mit einer GROUP BY-Klausel und dem Resultset eingegeben.

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

Durch die Verwendung von GROUP BY werden im Resultset Gruppen innerhalb von Gruppen gebildet.

Dies ist das Resultset.

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)

Die nächste Abfrage enthält mithilfe des CUBE-Operators eine GROUP BY-Klausel. Das Resultset sollte dieselben Informationen wie zuvor sowie eine Superaggregatzeile für jede GROUP BY-Spalte enthalten.

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

Das Resultset für den CUBE-Operator enthält dieselben Werte wie das Resultset der einfachen GROUP BY-Klausel aus dem ersten Beispiel und fügt die Superaggregate für jede Spalte in der GROUP BY-Klausel hinzu. NULL steht für alle Werte in der Menge, aus der das Aggregat berechnet wird.

Dies ist das Resultset.

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)

Zeile 4 des Resultsets zeigt an, dass insgesamt 150 Bestellungen für Filo Mix von allen Kunden aufgegeben wurden.

Zeile 11 des Resultsets zeigt an, dass die Gesamtzahl der für alle Produkte aufgegebenen Bestellungen 260 beträgt.

Die Zeilen 12-14 des Resultsets zeigen an, dass die Gesamtzahl der Bestellungen pro Kunden für alle Produkte 100, 110 und 50 beträgt.

V. Verwenden von CUBE für ein dreispaltiges Resultset

Im folgenden Beispiel gibt die SELECT-Anweisung die Produktmodell-ID, den Produktnamen und die Bestellmenge zurück. Die GROUP BY-Klausel in diesem Beispiel schließt die beiden Spalten ProductModelID und Name ein.

Mithilfe des CUBE-Operators enthält das Resultset genauere Informationen zur Bestellmenge für Produkte und Produktmodelle. NULL steht für alle Werte in der Titelspalte.

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

Das Erhöhen der Anzahl von Spalten in der GROUP BY-Klausel zeigt, warum der CUBE-Operator ein n-dimensionaler Operator ist. Eine GROUP BY-Klausel mit zwei Spalten gibt bei Verwendung des CUBE-Operators drei weitere Arten von Gruppen zurück. Die Anzahl von Gruppierungen kann, abhängig von den unterschiedlichen Werten in den Spalten, auch größer als drei sein.

Das Resultset ist nach Produktmodell-ID und dann nach Produktname gruppiert.

NULL in der ProductModelID-Spalte steht für alle ProductModels. NULL in der Name-Spalte steht für alle Products. Der CUBE-Operator gibt die folgenden Gruppen von Informationen aus einer SELECT-Anweisung zurück:

  • Die Bestellmenge für jedes Produktmodell
  • Die Bestellmenge für jedes Produkt
  • Die Gesamtzahl der Bestellungen

Jede in der GROUP BY-Klausel angegebene Spalte wurde mit allen anderen Spalten in der GROUP BY-Klausel in Beziehung gesetzt, und die SUM-Aggregatfunktion wurde erneut angewendet. Dies erzeugt zusätzliche Spalten im Resultset. Die Informationen im Resultset wachsen n-dimensional mit der Anzahl von in der GROUP BY-Klausel angegebenen Spalten.

ms187731.note(de-de,SQL.90).gifHinweis:
Stellen Sie sicher, dass die in der GROUP BY-Klausel aufgeführten Spalten in sinnvollen, praxisnahen Beziehungen zueinander stehen. Wenn Sie beispielsweise Name und ProductID verwenden, gibt der CUBE-Operator unbrauchbare Informationen zurück. Das Verwenden des CUBE-Operators für eine echte Hierarchie, wie z. B. die Verkaufszahlen eines Jahres oder eines Quartals, führt zu unbrauchbaren Zeilen im Resultset. Der ROLLUP-Operator eignet sich für diesen Zweck besser.

W. Verwenden der GROUPING-Funktion mit CUBE

Im folgenden Beispiel werden in der SELECT-Anweisung das SUM-Aggregat, die GROUP BY-Klausel und der CUBE-Operator verwendet. Darüber hinaus wird die GROUPING-Funktion auf die beiden hinter der GROUP BY-Klausel aufgeführten Spalten verwendet.

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

Das Resultset verfügt über zwei Spalten mit den Werten 0 und 1. Diese Werte werden von den Ausdrücken GROUPING(ProductModelID) und GROUPING(p.Name) erzeugt.

X. Verwenden des ROLLUP-Operators

Im folgenden Beispiel werden zwei Codebeispiele aufgeführt. Im ersten Beispiel werden Produktname, Kundenname und die Summe der aufgegebenen Bestellungen abgerufen und der ROLLUP-Operator verwendet.

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

Dies ist das Resultset.

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)

Das sich anschließende zweite Beispiel führt eine ROLLUP-Operation auf den Spalten company und department- aus und ermittelt die Gesamtzahl der Mitarbeiter.

Der ROLLUP-Operator erzeugt eine Summe von Aggregaten. Dies ist sinnvoll, wenn Summendaten benötigt werden, aber eine vollständige CUBE-Anweisung externe Daten bereitstellt oder Gruppen innerhalb von Gruppen vorliegen. So sind z. B. Abteilungen in einem Unternehmen eine Gruppe in einer Gruppe:

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 der folgenden Abfrage werden Firmenname, Abteilung und die Summe aller Angestellten des Unternehmens zusätzlich zu den ROLLUP-Berechnungen Teil des Resultsets.

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

Dies ist das Resultset.

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. Verwenden der GROUPING-Funktion

Im folgenden Beispiel werden der CubeExample-Tabelle drei neue Zeilen hinzugefügt. Jeder der drei Datensätze enthält in mindestens einer Spalte NULL, um zu zeigen, dass nur die ROLLUP-Funktion einen Wert von 1 in der Gruppierungsspalte erzeugt. Dieses Beispiel ändert darüber hinaus die SELECT-Anweisung aus dem vorherigen Beispiel.

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

Die GROUPING-Funktion kann nur zusammen mit CUBE und ROLLUP verwendet werden. Die GROUPING-Funktion gibt 1 zurück, wenn die Auswertung den Ausdruck NULL ergibt, weil der Spaltenwert NULL ist und für die Menge aller Werte steht. Die GROUPING-Funktion gibt 0 zurück, wenn die entsprechende Spalte (unabhängig davon, ob sie NULL ist oder nicht) als Syntaxwert nicht aus der Option CUBE oder ROLLUP stammt. Der Rückgabewert ist vom tinyint-Datentyp.

Dies ist das Resultset.

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. Verwenden von SELECT mit GROUP BY, einer Aggregatfunktion und ROLLUP

Im folgenden Beispiel wird eine SELECT-Abfrage mit einer Aggregatfunktion und einer GROUP BY-Klausel verwendet.

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

Im Resultset steht NULL für alle Werte in dieser Spalte.

Wenn Sie die SELECT-Anweisung ohne ROLLUP-Operator verwenden, erstellt die Anweisung eine einzelne Gruppe. Die Abfrage gibt für jede einzelne Kombination aus ProductModel, ProductModelID und ProductName eine Summe zurück:

ProductModel ProductModelID title SUM(qty)

Die GROUPING-Funktion kann zusammen mit dem ROLLUP-Operator oder dem CUBE-Operator verwendet werden. Sie können diese Funktion auf eine der Spalten in der Auswahlliste anwenden. Die Funktion gibt entweder 1 oder 0 zurück, abhängig davon, ob die Spalte durch den ROLLUP-Operator gruppiert wird.

a. Verwenden des INDEX-Optimiererhinweises

Im folgenden Beispiel werden zwei Möglichkeiten zum Verwenden des INDEX-Optimiererhinweises gezeigt. Das erste Beispiel zeigt, wie erzwungen werden kann, dass der Optimierer einen nicht gruppierten Index verwendet, um Zeilen aus einer Tabelle abzurufen. Im zweiten Beispiel wird ein Tabellenscan erzwungen, indem ein Index von 0 verwendet wird.

-- 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. Verwenden der OPTION- und GROUP-Hinweise

Im folgenden Beispiel wird gezeigt, wie die OPTION (GROUP)-Klausel in Verbindung mit einer GROUP BY-Klausel verwendet wird.

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. Verwenden des UNION-Abfragehinweises

Im folgenden Beispiel wird der MERGE UNION-Abfragehinweis verwendet.

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

d. Verwenden einer einfachen UNION-Klausel

Das Resultset im folgenden Beispiel enthält den Inhalt der Spalten ProductModelID und Name der Tabellen ProductModel und 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. Verwenden von SELECT INTO mit UNION

Im folgenden Beispiel gibt die INTO-Klausel in der zweiten SELECT-Anweisung an, dass die ProductResults-Tabelle das endgültige Resultset der Union der angegebenen Spalten aus den Tabellen ProductModel und Gloves enthält. Beachten Sie, dass die Gloves-Tabelle in der ersten SELECT-Anweisung erstellt wird.

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. Verwenden von UNION in zwei SELECT-Anweisungen mit ORDER BY

Die Reihenfolge bestimmter Parameter, die mit der UNION-Klausel verwendet werden, ist von Bedeutung. Im folgenden Beispiel wird die ordnungsgemäße und die falsche Verwendung von UNION in zwei SELECT-Anweisungen veranschaulicht, in denen eine Spalte in der Ausgabe umbenannt werden soll.

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. Verwenden von UNION mit drei SELECT-Anweisungen, wobei die Auswirkung von ALL und Klammern gezeigt wird

In den folgenden Beispielen wird UNION verwendet, um die Ergebnisse aus drei Tabellen zu kombinieren, wobei in allen Tabellen 5 identische Datenzeilen vorhanden sind. Im ersten Beispiel wird UNION ALL verwendet, um doppelte Datensätze anzuzeigen, und alle 15 Zeilen zurückgegeben. Im zweiten Beispiel wird UNION ohne ALL verwendet, um die doppelten Zeilen aus den kombinierten Ergebnissen der drei SELECT-Anweisungen zu löschen, und 5 Zeilen zurückgegeben.

Im dritten Beispiel wird ALL mit dem ersten UNION verwendet; das zweite UNION verwendet ALL nicht und ist in Klammern eingeschlossen. Da die zweite UNION-Anweisung in Klammern steht, wird sie zuerst verarbeitet; sie gibt 5 Zeilen zurück, weil die Option ALL nicht verwendet wird und Duplikate gelöscht werden. Diese 5 Zeilen werden mit den Ergebnissen der ersten SELECT-Anweisung mithilfe der UNION ALL-Schlüsselwörter kombiniert. Dabei werden die Duplikate in den beiden aus 5 Zeilen bestehenden Resultsets nicht gelöscht. Das Endergebnis enthält 10 Zeilen.

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

Siehe auch

Verweis

CREATE TRIGGER (Transact-SQL)
CREATE VIEW (Transact-SQL)
DELETE (Transact-SQL)
EXECUTE (Transact-SQL)
Ausdrücke (Transact-SQL)
INSERT (Transact-SQL)
LIKE (Transact-SQL)
UNION (Transact-SQL)
EXCEPT und INTERSECT (Transact-SQL)
UPDATE (Transact-SQL)
WHERE (Transact-SQL)

Andere Ressourcen

Verteilte Abfragen
Grundlegende Informationen zu Unterabfragen
Verwenden von Variablen und Parametern (Datenbankmodul)

Hilfe und Informationen

Informationsquellen für SQL Server 2005

Änderungsverlauf

Version Verlauf

14. April 2006

Neuer Inhalt:
  • Es wurde ein anderes Beispiel eingefügt, um die Verwendung von LIKE in der HAVING-Klausel zu verdeutlichen.