ROW_NUMBER()用法

 ROW_NUMBER()说明：返回结果集分区内行的序列号，每个分区的第一行从 1 开始。语法：ROW_NUMBER () OVER ( [ <partition_by_clause> ] <order_by_clause> ) 。备注：ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。参数：<partition_by_clause> ：将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。       <order_by_clause>：确定将 ROW_NUMBER 值分配给分区中的行的顺序。返回类型：bigint 。示例：/*以下示例将根据年初至今的销售额，返回 AdventureWorks 中销售人员的 ROW_NUMBER。*/USE AdventureWorksGOSELECT c.FirstName, c.LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', s.SalesYTD, a.PostalCodeFROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactIDJOIN Person.Address a ON a.AddressID = c.ContactIDWHERE TerritoryID IS NOT NULL AND SalesYTD <> 0/*FirstName  LastName    Row Number  SalesYTD      PostalCode---------  ----------  ----------  ------------  ----------------------------Shelley    Dyck        1           5200475.2313  98027Gail       Erickson    2           5015682.3752  98055Maciej     Dusza       3           4557045.0459  98027Linda      Ecoffey     4           3857163.6332  98027Mark       Erickson    5           3827950.238   98055Terry      Eminhizer   6           3587378.4257  98055Michael    Emanuel     7           3189356.2465  98055Jauna      Elson       8           3018725.4858  98055Carol      Elliott     9           2811012.7151  98027Janeth     Esteves     10          2241204.0424  98055Martha     Espinoza    11          1931620.1835  98055Carla      Eldridge    12          1764938.9859  98027Twanna     Evans       13          1758385.926   98055(13 行受影响)*/ /*以下示例将返回行号为 50 到 60（含）的行，并以 OrderDate 排序。*/ USE AdventureWorks;GOWITH OrderedOrders AS(SELECT SalesOrderID, OrderDate,ROW_NUMBER() OVER (order by OrderDate)as RowNumberFROM Sales.SalesOrderHeader ) SELECT * FROM OrderedOrders WHERE RowNumber between 50 and 60;/*SalesOrderID OrderDate               RowNumber------------ ----------------------- --------------------43708        2001-07-03 00:00:00.000 5043709        2001-07-03 00:00:00.000 5143710        2001-07-03 00:00:00.000 5243711        2001-07-04 00:00:00.000 5343712        2001-07-04 00:00:00.000 5443713        2001-07-05 00:00:00.000 5543714        2001-07-05 00:00:00.000 5643715        2001-07-05 00:00:00.000 5743716        2001-07-05 00:00:00.000 5843717        2001-07-05 00:00:00.000 5943718        2001-07-06 00:00:00.000 60(11 行受影响)*/