Usare funzioni di rango e funzioni per i set di righe
Le funzioni di rango e set di righe non sono funzioni scalari perché non restituiscono un singolo valore. Queste funzioni accettano un set di righe come input e restituiscono un set di righe come output.
Funzioni di classificazione
Le funzioni di classificazione consentono di eseguire calcoli su un set di righe definito dall'utente. Queste funzioni includono funzioni di classificazione, offset, aggregazione e distribuzione.
Questo esempio usa la funzione RANK per calcolare una classificazione basata su ListPrice, con il prezzo più alto classificato a 1:
SELECT TOP 100 ProductID, Name, ListPrice,
RANK() OVER(ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
ORDER BY RankByPrice;
I risultati della query potrebbero essere simili ai seguenti:
ProductID
Nome
Prezzo di listino
ClassificaPerPrezzo
749
Road-150 rosso, 62
3578.27
1
750
Road-150 Rosso, 44
3578.27
1
751
Road-150 rosso, 48
3578.27
1
771
Mountain-100 argento, 38
3399.99
4
772
Mountain-100 Argento, 42
3399.99
4
775
Mountain-100 nero, 38
3374.99
6
...
...
...
...
OVER
È possibile usare la clausola OVER per definire partizioni o raggruppamenti all'interno dei dati. Ad esempio, la query seguente estende l'esempio precedente per calcolare le classificazioni basate sui prezzi per i prodotti all'interno di ogni categoria.
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;
I risultati di questa query potrebbero avere un aspetto simile al seguente:
Categoria
Prodotto
Prezzo di listino
ClassificaPerPrezzo
Bib-Shorts
Men's Bib-Shorts, S
89.99
1
Bib-Shorts
Men's Bib-Shorts, M
89.99
1
Bike Racks
Hitch Rack - 4-Bike
120
1
Bike Stands
All-Purpose Supporto per biciclette
159
1
Bottles and Cages
Mountain Bottle Cage
9.99
1
Bottles and Cages
Road Bottle Cage
8,99
2
Bottles and Cages
Bottiglia d'acqua - 30 oz.
4.99
3
Bottom Brackets
HL Bottom Bracket
121.49
1
Bottom Brackets
ML Bottom Bracket
101.24
2
Bottom Brackets
LL Bottom Bracket
53.99
3
...
...
...
...
Annotazioni
Si noti che diverse righe hanno lo stesso valore di classificazione e alcuni valori vengono ignorati. Ciò è dovuto al fatto che viene usato solo RANK. A seconda del requisito, è possibile evitare legami con lo stesso valore di classificazione. È possibile controllare il valore di classificazione con altre funzioni, DENSE_RANK, NTILE e ROW_NUMBER, in base alle esigenze. Per informazioni dettagliate su queste funzioni, vedere la documentazione di riferimentoTransact-SQL.
Funzioni per i set di righe
Le funzioni del set di righe restituiscono una tabella virtuale che può essere usata nella clausola FROM come origine dati. Queste funzioni accettano parametri specifici della funzione del set di righe stesso. Includono OPENDATASOURCE, OPENQUERY, OPENROWSET, OPENXML e OPENJSON.
Le funzioni OPENDATASOURCE, OPENQUERY e OPENROWSET consentono di passare una query a un server di database remoto. Il server remoto restituirà quindi un set di righe di risultati. Ad esempio, la query seguente usa OPENROWSET per ottenere i risultati di una query da un'istanza di SQL Server denominata SalesDB.
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=SalesDB;Trusted_Connection=yes;',
'SELECT Name, ListPrice
FROM AdventureWorks.Production.Product') AS a;
Per usare i server remoti, è necessario abilitare alcune opzioni avanzate nell'istanza di SQL Server in cui si esegue la query.
Le funzioni OPENXML e OPENJSON consentono di eseguire query sui dati strutturati in formato XML o JSON ed estrarre valori in un set di righe tabulare.
Un'esplorazione dettagliata delle funzioni del set di righe esula dall'ambito di questo modulo. Per altre informazioni, vedere la documentazione di riferimentoTransact-SQL.