Använda ranknings- och raduppsättningsfunktioner
Ranknings- och raduppsättningsfunktioner är inte skalära funktioner eftersom de inte returnerar ett enda värde. Dessa funktioner accepterar en uppsättning rader som indata och returnerar en uppsättning rader som utdata.
Rankningsfunktioner
Med rankningsfunktioner kan du utföra beräkningar mot en användardefinierad uppsättning rader. Dessa funktioner omfattar ranknings-, offset-, aggregerings- och distributionsfunktioner.
I det här exemplet används funktionen RANK för att beräkna en rangordning baserat på ListPrice, med det högsta priset rankat på 1:
SELECT TOP 100 ProductID, Name, ListPrice,
RANK() OVER(ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
ORDER BY RankByPrice;
Frågeresultatet kan se ut så här:
Produkt-ID
Namn
Listpris
RankByPrice
749
Väg-150 Röd, 62
3578.27
1
750
Väg-150 Röd, 44
3578.27
1
751
Väg-150 Röd, 48
3578.27
1
771
Mountain-100 Silver, 38
3399.99
4
772
Mountain-100 Silver, 42
3399.99
4
775
Mountain-100 Svart, 38
3374.99
6
...
...
...
...
ÖVER
Du kan använda OVER-satsen för att definiera partitioner eller grupper i data. Följande fråga utökar till exempel föregående exempel för att beräkna prisbaserade rankningar för produkter inom varje kategori.
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;
Resultatet av den här frågan kan se ut ungefär så här:
Kategori
Produkt
Listpris
RankByPrice
Bib-Shorts
Herrbib-shorts, S
89.99
1
Bib-Shorts
Herrbib-shorts, M
89.99
1
Cykelställ
Cykelhållare för 4 cyklar
120
1
Cykelställ
All-Purpose cykelställ
159
1
Flaskor och burar
Mountain-flaskhållare
9.99
1
Flaskor och burar
Cykelflaskhållare
8.99
2
Flaskor och burar
Vattenflaska - 30 oz.
4,99
3
Nedre hakparenteser
HL,nedre hakparentes
121.49
1
Nedre hakparenteser
ML Vevlager
101.24
2
Nedre hakparenteser
LL Vevlager
53.99
3
...
...
...
...
Anmärkning
Observera att flera rader har samma rangvärde och att vissa värden har hoppats över. Det beror på att vi endast använder RANK. Beroende på kravet kanske du vill undvika band med samma rankningsvärde. Du kan styra rangvärdet med andra funktioner, DENSE_RANK, NTILE och ROW_NUMBER efter behov. Mer information om dessa funktioner finns i referensdokumentationen förTransact-SQL.
Raduppsättningsfunktioner
Raduppsättningsfunktioner returnerar en virtuell tabell som kan användas i FROM-satsen som en datakälla. Dessa funktioner tar parametrar som är specifika för själva raduppsättningsfunktionen. De omfattar OPENDATASOURCE, OPENQUERY, OPENROWSET, OPENXML och OPENJSON.
Med funktionerna OPENDATASOURCE, OPENQUERY och OPENROWSET kan du skicka en fråga till en fjärrdatabasserver. Fjärrservern returnerar sedan en uppsättning resultatrader. Följande fråga använder till exempel OPENROWSET för att hämta resultatet av en fråga från en SQL Server-instans med namnet SalesDB.
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=SalesDB;Trusted_Connection=yes;',
'SELECT Name, ListPrice
FROM AdventureWorks.Production.Product') AS a;
Om du vill använda fjärrservrar måste du aktivera vissa avancerade alternativ i SQL Server-instansen där du kör frågan.
Med funktionerna OPENXML och OPENJSON kan du köra frågor mot strukturerade data i XML- eller JSON-format och extrahera värden till en tabellraduppsättning.
En detaljerad utforskning av raduppsättningsfunktioner ligger utanför omfånget för den här modulen. Mer information finns i referensdokumentationen förTransact-SQL.