Использование ранжирующих функций и функций наборов строк.
Ранжирующая функции и функция наборов строк не являются скалярными, так как они не возвращают одно значение. Эти функции принимают набор строк в качестве входных данных и возвращают набор строк в качестве выходных данных.
Ранжирующие функции
Ранжирующие функции позволяют выполнять вычисления с пользовательским набором строк. К этим функциям относятся функции ранжирования, смещения, агрегирования и распределения.
В этом примере для вычисления ранжирования на основе ListPrice используется функция RANK (наивысшее значение цены 1):
SELECT TOP 100 ProductID, Name, ListPrice,
RANK() OVER(ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
ORDER BY RankByPrice;
Результаты запроса могут выглядеть следующим образом:
ProductID
Имя.
ПрейскурантнаяЦена
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
Для определения секций или выполнения группирования в пределах данных можно использовать предложение OVER. Например, следующий запрос расширяет предыдущий пример, позволяя рассчитать ранжирование на основе цен для продуктов каждой категории.
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;
Результаты этого запроса могут выглядеть примерно так:
Категория
Продукт
ПрейскурантнаяЦена
RankByPrice
Велошорты
Мужские полукомбинезоны, размер S
89,99
1
Велошорты
Мужские полукомбинезоны, размер M
89,99
1
Багажники для велосипедов
Велосипедная стойка на 4 велосипеда
120
1
Велосипедные стойки
Универсальная подставка для велосипеда
159
1
Бутылки и корзины
Mountain Bottle Cage
9,99
1
Бутылки и корзины
Дорожный держатель для бутылки
8.99
2
Бутылки и корзины
Фляга для воды — 30 унций.
4,99
3
Каретки
Велосипедная каретка HL
121,49
1
Каретки
Велосипедная каретка ML
101,24
2
Каретки
Велосипедная каретка LL
53,99
3
...
...
...
...
Примечание.
Обратите внимание, что несколько строк имеют одно и то же значение приоритета, а некоторые значения пропускаются. Это связано с тем, что мы используем только RANK. В зависимости от требований может потребоваться избегать связей с одинаковым значением приоритета. При необходимости можно управлять значением приоритета с помощью других функций: DENSE_RANK, NTILE и ROW_NUMBER. Дополнительные сведения об этих функциях см. в справочной документации по Transact-SQL.
Функции наборов строк
Функции набора строк возвращают виртуальную таблицу, которую можно использовать в предложении FROM в качестве источника данных. Эти функции принимают параметры, характерные для самой функции набора строк. В их число входят следующие: OPENDATASOURCE, OPENQUERY, OPENROWSET, OPENXML и OPENJSON.
Функции OPENDATASOURCE, OPENQUERY и OPENROWSET позволяют передавать запрос на удаленный сервер базы данных. Затем удаленный сервер возвратит набор строк результатов. Например, следующий запрос использует OPENROWSET для получения результатов запроса из экземпляра SQL Server с именем SalesDB.
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=SalesDB;Trusted_Connection=yes;',
'SELECT Name, ListPrice
FROM AdventureWorks.Production.Product') AS a;
Чтобы использовать удаленные серверы, необходимо включить некоторые дополнительные параметры в экземпляре SQL Server, где выполняется запрос.
Функции OPENXML и OPENJSON позволяют запрашивать структурированные данные в формате XML или JSON и извлекать значения в табличный набор строк.
Подробное описание функций набора строк выходит за рамки этого модуля. Дополнительные сведения см. в справочной документации по Transact-SQL.