Verwenden von Rangfolge- und Rowsetfunktionen
Rangfolge- und Rowsetfunktionen sind keine Skalarfunktionen, da sie keinen einzelnen Wert zurückgeben. Diese Funktionen verwenden mehrere Zeilen als Eingabe und geben mehrere Zeilen als Ausgabe zurück.
Rangfolgefunktionen
Mit Rangfolgefunktionen können Sie Berechnungen für einen benutzerdefinierten Satz von Zeilen ausführen. Diese Funktionen umfassen Rangfolge-, Offset-, Aggregat- und Verteilungsfunktionen.
In diesem Beispiel wird die RANK-Funktion verwendet, um eine Rangfolge basierend auf dem ListPrice-Wert zu berechnen, wobei der höchste Preis bei 1 liegt:
SELECT TOP 100 ProductID, Name, ListPrice,
RANK() OVER(ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
ORDER BY RankByPrice;
Die Abfrageergebnisse können wie folgt aussehen:
ProductID
Name
ListPrice
RankByPrice
749
Road-150 Red, 62
3578.27
1
750
Road-150 Red, 44
3578.27
1
751
Road-150 Red, 48
3578.27
1
771
Mountain-100 Silver, 38
3399.99
4
772
Mountain-100 Silver, 42
3399.99
4
775
Mountain-100 Black, 38
3374.99
6
...
...
...
...
OVER
Sie können die OVER-Klausel verwenden, um Partitionen oder Gruppierungen innerhalb der Daten zu definieren. Die folgende Abfrage erweitert beispielsweise das vorherige Beispiel, um preisbasierte Rangfolgen für Produkte innerhalb jeder Kategorie zu berechnen.
SELECT c.Name AS Category, p.Name AS Product, ListPrice,
RANK() OVER(PARTITION BY c.Name ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
JOIN Production.ProductCategory AS c
ON p.ProductCategoryID = c.ProductcategoryID
ORDER BY Category, RankByPrice;
Die Ergebnisse dieser Abfrage können ungefähr so aussehen:
Category
Produkt
ListPrice
RankByPrice
Trägershorts
Men's Bib-Shorts, S
89.99
1
Trägershorts
Men's Bib-Shorts, M
89.99
1
Fahrradträger
Hitch Rack - 4-Bike
120
1
Fahrradständer
All-Purpose Bike Stand
159
1
Trinkflaschen und Körbe
Mountain Bottle Cage
9.99
1
Trinkflaschen und Körbe
Road Bottle Cage
8,99
2
Trinkflaschen und Körbe
Water Bottle - 30 oz.
4,99
3
Tretlager
HL Bottom Bracket
121.49
1
Tretlager
ML Bottom Bracket
101.24
2
Tretlager
LL Bottom Bracket
53.99
3
...
...
...
...
Hinweis
Beachten Sie, dass mehrere Zeilen den gleichen Rangwert aufweisen und einige Werte übersprungen werden. Dies liegt daran, dass nur RANK verwendet wird. Abhängig von der jeweiligen Anforderung kann es wünschenswert sein, Bindungen mit demselben Rangwert zu vermeiden. Sie können den Rangwert mit anderen Funktionen wie DENSE_RANK, NTILE und ROW_NUMBER je nach Bedarf steuern. Ausführliche Informationen zu diesen Funktionen finden Sie in der Transact-SQL-Referenzdokumentation.
Rowsetfunktionen
Rowsetfunktionen geben eine virtuelle Tabelle zurück, die in der FROM-Klausel als Datenquelle verwendet werden kann. Diese Funktionen nehmen Parameter an, die für die Rowsetfunktion selbst spezifisch sind. Dazu gehören OPENDATASOURCE, OPENQUERY, OPENROWSET, OPENXML und OPENJSON.
Mit den OPENDATASOURCE-, OPENQUERY- und OPENROWSET-Funktionen können Sie eine Abfrage an einen Remotedatenbankserver übergeben. Der Remoteserver gibt dann eine Reihe von Ergebniszeilen zurück. Die folgende Abfrage verwendet z. B. OPENROWSET, um die Ergebnisse einer Abfrage aus einer SQL Server-Instanz namens SalesDB abzurufen.
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=SalesDB;Trusted_Connection=yes;',
'SELECT Name, ListPrice
FROM AdventureWorks.Production.Product') AS a;
Um Remoteserver zu verwenden, müssen Sie einige erweiterte Optionen in der SQL Server-Instanz aktivieren, in der Sie die Abfrage ausführen.
Mit den OPENXML- und OPENJSON-Funktionen können Sie strukturierte Daten im XML- oder JSON-Format abfragen und Werte in ein tabellarisches Rowset extrahieren.
Eine ausführliche Untersuchung von Rowsetfunktionen würde den Rahmen dieses Moduls sprengen. Weitere Informationen finden Sie in der Transact-SQL-Referenzdokumentation.