本帖最后由 吴步兵 于 2012-11-9 22:56 编辑
这些函数被用于以与结果集顺序无关的特定顺序,枚举已排序的或排在前面的结果集。 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
复制代码结果为: |