黑马程序员之SQL学习笔记: SQL排列函数

--------------------------------------------------- 2345王牌技术员联盟、 2345王牌技术员联盟、期待与您交流！---------------------------------------------------------

ROW_NUMBER()函数

ROW_NUMBER()函数根据作为参数传递给这个函数的ORDER BY子句的值，返回一个不断递增的整数值。如果ROW_NUMBER的ORDER BY的值和结果集中的顺序相匹配，返回值将是递增的，以升序排列。如果ROW_NUMBER的ORDER BY子句的值和结果集中的顺序不同，这些值将不会按顺序列出，但它们表示ROW_NUMBER函数的ORDER BY子句的顺序。如下面的例子和结果所示
SELECT ProductCategoryID ,Name ,ROW_NUMBER() OVER (ORDER BY Name) AS RowNum FROM Production.ProductCategory ORDER BY Name

由于ROW_NUMBER()调用中的ORDERBY子句和查询结果的顺序匹配，所以对这些结果按顺序列出，如下图所示：

不过，在函数调用中使用另一个ORDER BY子句时，这些值就是无序的了。
SELECT ProductCategoryID ,Name ,ROW_NUMBER() OVER (ORDER BY Name) AS RowNum FROM Production.ProductCategory ORDER BY ProductCategoryID

这是了解如何使用ORDER BY子句对结果进行排序的有效方法。如下图所示：

RANK()与DENSE_RANK()函数

这两个函数与ROW_NUMBER()函数类似，因为它们都返回一个基于ORDER BY子句的值。不过这些值不一定永远是唯一的。排列值对于所提供的ORDER BY子句中的重复结果而言也是重复的，而且唯一性是仅仅基于ORDER BY列表中的唯一值的。这些函数用不同的方法来处理重复的值。RANK()函数保留列表中行的位置序号，对于每个重复的值，该函数会跳过下面与其相邻的值，于是就可以将下一个不重复的值保留在正确的位置上。

其行为类似于短跑比赛中的并列成绩。例如刘翔与Dayron Robles（古巴）在110栏的比赛中都跑出了12’92的成绩，那他们就是并列第一，而其后的一名选手将会获得第三名的成绩。
SELECT ProductID ,Name,ListPrice,RANK() OVER (ORDER BY ListPrice DESC) AS [Rank] FROM Production.Product ORDER BY [Rank]

注意在下图的结果列表中，重复的价格值所对应的结果是相同的，而每个连接之后的值都被跳过了。比如，产品"Road-150 Red, 52"和"Road-150 Red, 56"都排在第1，而接下来的行"Mountain-100 Silver,38"就排在第6了。

DENSE_RANK()函数的工作方式与RANK()函数相同，不过它不会跳过每个连接后的值，这样就不会有值被跳过了，但是在连接处排列序号位置将会丢失。
SELECT ProductID ,Name,ListPrice,DENSE_RANK() OVER (ORDER BY ListPrice DESC) AS [Rank] FROM Production.Product ORDER BY [Rank]

下图的结果重复了排列值，但是不会跳过列中的任何数字。

NTILE(n)函数

这个函数也用于对结果进行排列，并返回一个整型的排列值，但是它不会对结果以唯一的排列顺序进行枚举，而是将结果切分为有限数量的排列组。比如，一个表有10 000行，使用1000为参数值调用NTILE()函数，即NTILE(1000)，并将结果分成以10为单位的1000个组，每个组赋予相同的排列值。和本节讨论的其他排列函数一样，NTILE()函数也支持OVER(ORDER BY…)语法。下面的例子根据产品价格，按照从高到低的顺序把Product表分为50组产品：
SELECT ProductID ,Name,ListPrice,NTILE(50) OVER (ORDER BY ListPrice DESC) AS GroupedProducts FROM Production.Product ORDER BY GroupedProducts

结果为：

--------------------------------------------------- 2345王牌技术员联盟、 2345王牌技术员联盟、期待与您交流！---------------------------------------------------------