SQL中row_number()rank()dense_rank()ntile()函数详解.doc

row_number( )、rank( )、dense_rank( )、ntile( )函数 SQL 几个新的排序(排名)函数,如ROW_NUMBER、RANK、DENSE_RANK等。这些新函数使您可以有效地分析数据以及向查询的结果行提供排序值。 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 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。*/USEAdventureWorksGOSELECT 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 排序。*/USEAdventureWorks;GOWITH OrderedOrders AS(SELECT SalesOrderID, OrderDate,ROW_NUMBER() OVER (order by OrderDate)as RowNumberFROMSales.SalesOrderHeader ) SELECT *FROM OrderedOrders WHERE RowNumber between 50 and 60;/*S