ROW_NUMBER

Tsql теория > ROW_NUMBER
31.05.2018 14:06:59



Статья:

Приведенный ниже запрос возвращает четыре системные таблицы в алфавитном порядке.

SQL
SELECT 
  name, recovery_model_desc
FROM sys.databases 
WHERE database_id < 5
ORDER BY name ASC;

Ниже приводится результирующий набор.

name recovery_model_desc
master SIMPLE
model FULL
msdb SIMPLE
tempdb SIMPLE

Чтобы добавить столбец с номерами строк перед каждой строкой, добавьте столбец с помощью функции ROW_NUMBER, в данном случае с именем Row#. Предложение ORDER BYнеобходимо переместить к предложению OVER.

SQL
SELECT 
  ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#,
  name, recovery_model_desc
FROM sys.databases 
WHERE database_id < 5;

Ниже приводится результирующий набор.

Номер строки name recovery_model_desc
1 master SIMPLE
2 model FULL
3 msdb SIMPLE
4 tempdb SIMPLE

Добавление предложения PARTITION BY для столбца recovery_model_desc приведет к тому, что нумерация начнется заново при изменении значения recovery_model_desc.

SQL
SELECT 
  ROW_NUMBER() OVER(PARTITION BY recovery_model_desc ORDER BY name ASC) 
    AS Row#,
  name, recovery_model_desc
FROM sys.databases WHERE database_id < 5;

Ниже приводится результирующий набор.

Номер строки name recovery_model_desc
1 model FULL
1 master SIMPLE
2 msdb SIMPLE
3 tempdb SIMPLE

Б. Возврат номера строки для salespeople

В следующем примере показан расчет номера строки для salespeople в Компания Adventure Works Cycles, выполняемый на основе ранжирования продаж за текущий год.

SQL
USE AdventureWorks2012;   
GO  
SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row,   
    FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD"   
FROM Sales.vSalesPerson  
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;  

Ниже приводится результирующий набор.


Row FirstName    LastName               SalesYTD  
--- -----------  ---------------------- -----------------  
1   Linda        Mitchell               4251368.54  
2   Jae          Pak                    4116871.22  
3   Michael      Blythe                 3763178.17  
4   Jillian      Carson                 3189418.36  
5   Ranjit       Varkey Chudukatil      3121616.32  
6   Jose         Saraiva                2604540.71  
7   Shu          Ito                    2458535.61  
8   Tsvi         Reiter                 2315185.61  
9   Rachel       Valdez                 1827066.71  
10  Tete         Mensa-Annan            1576562.19  
11  David        Campbell               1573012.93  
12  Garrett      Vargas                 1453719.46  
13  Lynn         Tsoflias               1421810.92  
14  Pamela       Ansman-Wolfe           1352577.13  

В. Возврат подмножества строк

В следующем примере показан расчет номеров всех строк в таблице SalesOrderHeader в порядке OrderDate с последующим возвращением строк с номерами от 50 до 60включительно.

SQL
USE AdventureWorks2012;  
GO  
WITH OrderedOrders AS  
(  
    SELECT SalesOrderID, OrderDate,  
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNumber  
    FROM Sales.SalesOrderHeader   
)   
SELECT SalesOrderID, OrderDate, RowNumber    
FROM OrderedOrders   
WHERE RowNumber BETWEEN 50 AND 60;  

Г. Использование ROW_NUMBER() с PARTITION

В следующем примере аргумент PARTITION BY используется для секционирования результирующего набора запроса по столбцу TerritoryName. Предложение ORDER BY, указанное в предложении OVER, упорядочивает строки каждой секции по столбцу SalesYTD.Предложение ORDER BY в инструкции SELECT упорядочивает полный результирующий набор запроса по TerritoryName.

SQL
USE AdventureWorks2012;  
GO  
SELECT FirstName, LastName, TerritoryName, ROUND(SalesYTD,2,1) AS SalesYTD,  
ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) 
  AS Row  
FROM Sales.vSalesPerson  
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0  
ORDER BY TerritoryName;  

Ниже приводится результирующий набор.


FirstName  LastName             TerritoryName        SalesYTD      Row  
---------  -------------------- ------------------   ------------  ---  
Lynn       Tsoflias             Australia            1421810.92    1  
Jose       Saraiva              Canada               2604540.71    1  
Garrett    Vargas               Canada               1453719.46    2  
Jillian    Carson               Central              3189418.36    1  
Ranjit     Varkey Chudukatil    France               3121616.32    1  
Rachel     Valdez               Germany              1827066.71    1  
Michael    Blythe               Northeast            3763178.17    1  
Tete       Mensa-Annan          Northwest            1576562.19    1  
David      Campbell             Northwest            1573012.93    2  
Pamela     Ansman-Wolfe         Northwest            1352577.13    3  
Tsvi       Reiter               Southeast            2315185.61    1  
Linda      Mitchell             Southwest            4251368.54    1  
Shu        Ito                  Southwest            2458535.61    2  
Jae        Pak                  United Kingdom       4116871.22    1